Power Query Hack vs Pivot Table with Dynamic Arrays - Excel Report

When you create an advanced report in Excel, you might have different options or combination of tools.

In this tutorial, I create an advanced Excel report using Pivot tables along with Dynamic Array Functions. Then I create the same report using a little trick in Power Query.
Let me know in a comment which method you prefer.
Now, let’s have a look.

To book a Corporate Training anywhere in the World check

Here is a contribution from a Friend to the Channel (Thank you Ivan Cortinas):
We add the table to the data model. We create 2 measures.
If the name of your table is Group3...
TotalSales:=SUM(Group3[Sales])
SalesCustomerList:=CONCATENATEX(VALUES(Group3[Customer]),Group3[Customer],", ")
Then you create Pivot Table from Data Model and move Region and Manager to Rows and move the measures TotalSales and SalesCustomerList to values.

[marcosbrooksallen]

Very good Tutorial. Nice Techniques. I rather best the M Code in Power Query just because I can reuse the exported data to add to the data model and use it for other reports. Thank you Nabil for your contributions to the community. They are much appreciated.

[IvanCortinas_ES]

Both methods are excellent.
I prefer the M language method with Power Query.
A third option would be to use the Power Pivot Data Model and have created a measure to get the unique and separate Clients with ", " with the CONCATENATEX and VALUES functions.
Excellent tutorial Nabil!!!

[lellachkoune]

thank you very much for your very good job. I prefer the power query fonction because to my mind, it is easier to update data and get more different views of them

[cent.ugurdag]

Hi, I love your tutorials, the 2nd method with m code is difficult if you dont master it, the first way with pivot table and array functions is a very nice classic solution with excel, greetings from Turkey Bodrum

[nadermounir]

Do u offer courses for individuals also ?

[nadermounir]

Can we have a video using Power Pivot to solve this ? Would be greatly appreciated 😊

[QasimAli-gynb]

Hi Nabil, what tools you are using for recording and editing your videos, please.

[gandhisunil]

I prefer power query method out of these two

[JoseAntonioMorato]

Dear Nabil,
I share a formula with dynamic matrices that is equivalent to the one created by Power Query:

VSTACK({"Region", "Manager", "Customer", "Total Sales"}, HSTACK(Region, Manager, Array, TotalSales)))🤗
Note: The ranges G5:G52, H5:H52 and I5:I52 correspond to the pivot table rows with the data I created.

[nadermounir]

Dynamic arrays and pivot tables are much easier

[JoseAntonioMorato]

I regret your decision to no longer make the workbooks available for download, as it is difficult to create data similar to what appears in the video, so that we can practice your teachings. .😒