Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
6m ago
Hi to all!
Thanks a lot for receive me in this forum. Is my first message here! I hope you can help me about the issue.
I need to transform with Power Query from this:
* | A | B | C |
3 | Company | Origin | Destiny |
4 | AA | Barcelona | Valencia |
5 | AA | Valencia | Barcelona |
6 | AA | Madrid | Roma |
7 | AA | Roma | Madrid |
8 | AA | Barcelona | Madrid |
9 | AA | Madrid | Barcelona |
10 | AB | Barcelona | Valencia |
11 | AB | Valencia | Barcelona |
12 | AC | Madrid | Roma |
13 | AC | Roma | Madrid |
14 | AC | Barcelona | Roma |
15 | AC | Roma | Barcelona |
Into This:
* | E | F | G | H | I |
3 | Cities | No. Of Destinies | Destiny 1 | Destiny 2 | Destiny 3 |
4 | Barcelona | 3 | Valencia | Madrid | Roma |
5 | Valencia | 1 | Barcelona | ||
6 | Madrid | 2 | Roma | Barcelona | |
7 | Roma | 2 | Madrid | Barcelona |
I already know how to do it with formulas and vba. But I wanna know how can I get this result with Power Query. Thx in advanced for your answers. Blessings!
Solved! Go to Solution.
then we take this code instead (it will ignore all columns that are not relevant to the results):
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHSUXJ0BBJOiUXJqTn5eYlAdlhiTmpecmaiUqxOtJIpTAVcFFkxSIUZTIVvYkpRZgqQEZSfC5Eyh0mBReAqQFIW2GxGkrfEMBXVVkMDkAInfE43NIQpwel2QyOQEmesjjc0hslhut7QBCaHbDtCqymaViRbYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Company = _t, Origin = _t, Destiny = _t]), Group = Table.Group(Source, {"Origin"}, {{"Count", each List.Count(List.Distinct(_[Destiny])), type number}, {"Destinations", each Table.FromRows({List.Distinct(_[Destiny])}), type table}}), AllColumnNames = Table.ColumnNames(Table.Combine(Group[Destinations])), #"Expanded Destinations" = Table.ExpandTableColumn(Group, "Destinations", AllColumnNames, AllColumnNames) in #"Expanded Destinations"
The step "Group" will chunk the table up per "Origin" and return 2 columns: "Count" with the count of all distinct destinations and "Destinations" with a table per row containing a column with all distinct destinations.
These nested functions need to be read "inside-out" like in Excel: So for the "Count" we start with _[Destiny] which selects the column "Destiny" from the table and returns it it List-format. So we take "List.Distinct" in order to reduce it to distinct values only. Then a List.Count on it.
In order to return the list of distinct destinations in a table-format (quasi-transpose), we use Table.FromRows, as this is just made for this job. Just be aware that it could also create a table from multiple rows, so the format of the input-parameter it expects is a list of lists (therefore the additional curly brackets).
Now we have the content we need, but we need to expand the table that has been returned: If you would do that manually, a command would be created that let's you open all columns of the table in the first row and these column names would be hardcoded. So in order to avoid that, we create a list of column names that is dynamic, retrieving all column names from all tables. Therefore we combine all tables into one big one (Table.Combine) and read out the column names from it (Table.ColumnNames) in step "AllColumnNames". We use this as a variable for our last step "#"Expanded Destinations".
The key to write and read such a compact code lies in understanding the specific value types (https://msdn.microsoft.com/en-us/library/mt186367.aspx) of the necessary input- & output-parameters of the functions that you can find here: https://msdn.microsoft.com/en-us/library/mt253322.aspx?f=255&MSPPError=-2147217396
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |