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.
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?
Solved! Go to Solution.
Hi @gerasimovav
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
Hi @gerasimovav
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
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
Looking forward for your help.
Regards,
VIshal.
@gerasimovavindeed it is:)
can you guide me on how to do that particular step in query editor?
@vishal17081990 I think this step is only aviable in Advanced Editor.
You can get the similar result in query editor by clicking OrderNum and choose Group By then choose Operation "All Rows".
The result is
Table.Group(#"Changed Type", {"OrderNum"}, {{"Count", each _, type table}})
let's compare with
Table.Group(#"Changed Type", {"OrderNum"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}})
The results are similar but without indexes, which are highly important for us.
@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.
great solution! but how would this be done in the query editor?
@gerasimovav 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)
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |