Forum Discussion
Group by a column with expansion
Hello coleages.
I have such table:
ClientID Name Surname OrderNum OrderDate PartNum Item Name Count
134 Alex Jones 45 21.12.2017 45345 T-Shirt 3
134 Alex Jones 45 21.12.2017 7643 Cap 2
36 Sam Grey 55 27.12.2017 5436 Sticker 6
36 Sam Grey 55 27.12.2017 5498 Slicer 1
39 Michel Salgado 42 28.12.2017 3342 Book 3
there are one order consist of a several items (Order 45 includes 3 T-Shirts and 2 Caps)
I have to get the table:
Order Num ClientId OrderDate 1-st item 2-nd item 3-rd item 4-th item 5-th item
45 134 21.12.2017 45345 2 T-Shirt 7643 2 Cap None None None
55 36 27.12.2017 5436 6 Sticker 5498 1 Slicer None None None
42 39 28.12.2017 3342 3 Book None None None None
How can I done it?
Hi Anonymous
Steps below:
1.Add an index column based on “Count” column group by the “OrderNum” column.
2.Merge “PartNum”, “Count”, “Item Name” columns
3.Add conditional columns
4.Fill up null values
5.Remove Duplicates
You could click the icon on the APPLIED STEPS pane, then you can see how to set the steps.
Code in Advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY8/D8IgEMW/imGujXDQP2N1MDFxqlvTgSCxpCgGO+i39ziWjrrce3nc7+UYBsZBsoJ13r5RTuFhX6hS4RC85KIUO15vKAJKL9t+cnFBB2wsfufrSgLKQT/TE6FQoe31Hecx2g+KIq5ec0rmtcWZ2UZ01T9s26Q17wyhPKMt2rMzk/XU4W/6GtLRIhU06wIACvchzPnD4xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Name = _t, Surname = _t, OrderNum = _t, OrderDate = _t, PartNum = _t, #"Item Name" = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Name", type text}, {"Surname", type text}, {"OrderNum", Int64.Type}, {"OrderDate", type text}, {"PartNum", Int64.Type}, {"Item Name", type text}, {"Count", Int64.Type}}), Partition = Table.Group(#"Changed Type", {"OrderNum"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ClientID", "Name", "Surname", "OrderNum", "OrderDate", "PartNum", "Item Name", "Count", "Index"}, {"ClientID", "Name", "Surname", "OrderNum.1", "OrderDate", "PartNum", "Item Name", "Count", "Index"}), #"Inserted Merged Column" = Table.AddColumn(#"Expanded Partition", "Merged", each Text.Combine({Text.From([PartNum], "en-US"), Text.From([Count], "en-US"), [Item Name]}, " "), type text), #"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column", "1-st item", each if [Index] = 1 then [Merged] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "2-nd item", each if [Index] = 2 then [Merged] else null), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "3-rd item", each if [Index] = 3 then [Merged] else null), #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "4-th item", each if [Index] = 4 then [Merged] else null), #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "5-th item", each if [Index] = 5 then [Merged] else null), #"Filled Up" = Table.FillUp(#"Added Conditional Column4",{"2-nd item", "3-rd item", "4-th item"}), #"Removed Duplicates" = Table.Distinct(#"Filled Up", {"OrderNum"}) in #"Removed Duplicates"Best Regards
Maggie
9 Replies
Anonymous you can do this in power query.
1) Click on edit query in power bi and then go to the table which you want to pivot
2) In the transform tab there is a pivot column click on that (https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks)
v-juanli-msftCommunity Support
Hi Anonymous
Steps below:
1.Add an index column based on “Count” column group by the “OrderNum” column.
2.Merge “PartNum”, “Count”, “Item Name” columns
3.Add conditional columns
4.Fill up null values
5.Remove Duplicates
You could click the icon on the APPLIED STEPS pane, then you can see how to set the steps.
Code in Advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY8/D8IgEMW/imGujXDQP2N1MDFxqlvTgSCxpCgGO+i39ziWjrrce3nc7+UYBsZBsoJ13r5RTuFhX6hS4RC85KIUO15vKAJKL9t+cnFBB2wsfufrSgLKQT/TE6FQoe31Hecx2g+KIq5ec0rmtcWZ2UZ01T9s26Q17wyhPKMt2rMzk/XU4W/6GtLRIhU06wIACvchzPnD4xc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientID = _t, Name = _t, Surname = _t, OrderNum = _t, OrderDate = _t, PartNum = _t, #"Item Name" = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Name", type text}, {"Surname", type text}, {"OrderNum", Int64.Type}, {"OrderDate", type text}, {"PartNum", Int64.Type}, {"Item Name", type text}, {"Count", Int64.Type}}), Partition = Table.Group(#"Changed Type", {"OrderNum"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"ClientID", "Name", "Surname", "OrderNum", "OrderDate", "PartNum", "Item Name", "Count", "Index"}, {"ClientID", "Name", "Surname", "OrderNum.1", "OrderDate", "PartNum", "Item Name", "Count", "Index"}), #"Inserted Merged Column" = Table.AddColumn(#"Expanded Partition", "Merged", each Text.Combine({Text.From([PartNum], "en-US"), Text.From([Count], "en-US"), [Item Name]}, " "), type text), #"Added Conditional Column" = Table.AddColumn(#"Inserted Merged Column", "1-st item", each if [Index] = 1 then [Merged] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "2-nd item", each if [Index] = 2 then [Merged] else null), #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "3-rd item", each if [Index] = 3 then [Merged] else null), #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "4-th item", each if [Index] = 4 then [Merged] else null), #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "5-th item", each if [Index] = 5 then [Merged] else null), #"Filled Up" = Table.FillUp(#"Added Conditional Column4",{"2-nd item", "3-rd item", "4-th item"}), #"Removed Duplicates" = Table.Distinct(#"Filled Up", {"OrderNum"}) in #"Removed Duplicates"Best Regards
Maggie
v-juanli-msft, greatest thanks!
It looks like a magic!
But there is mistake in “Filled Up” Stage which I can’t still avoid.
If we firstly sort by ClientID, we’ve got:
Before “Filled Up”:
After:
2-nd item of the 45 Order jumps to the 42 Order :(
We need to fill to only same Order, not to all empty rows ...
I've avoided this mistake by create another table. The First Column is OrderNum, the second is 1-st item, the third is 2-nd item ... The OrderNum is linked with OrderNum of v-juanli-msft result without last 2 incorrect steps
#"Filled Up" = Table.FillUp(#"Added Conditional Column4",{"2-nd item", "3-rd item", "4-th item"}), #"Removed Duplicates" = Table.Distinct(#"Filled Up", {"OrderNum"}). The second and other columns look like 1-st item = LASTNONBLANK('List1'[1-st item];0).
And the task has solved absolutely.
vishal17081990Helper I
Hi v-juanli-msft,
i was going through your post as am facing similar kind of scenario,
However i wonder if you could explain 3 rd step i.e. partition, after that step whole table is transformed into merged columns with orderNum
Step Partition
Looking forward for your help.
Regards,
VIshal.