Forum Discussion
How To Rearrange Data Set
Hello Team!
Could someone give me an idea of how I can rearrange the data set (see below)?
The data set has three types of lines:
H - header
L - line
V - "details"
For each invoice (column B) the set should contain H line first, then L lines (sorted by "id" in column C). Not all L lines have related V lines. If there are V lines, they should follow their "parent" L line (sorted by "id" in column D).
I think the image below is pretty descriptive. Let me know if it is not.
Thank you in advance for your feedback.
I've solved it myself 🙂
Probably not the most elegant solution, but it seems to work.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Invoice", each _, type table [Column1=nullable text, Column2=nullable number, Column3=nullable number, Column4=nullable number, Column5=nullable text, Column6=nullable number]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "InvoiceIndex", 1, 1, Int64.Type),
#"Expanded Invoice" = Table.ExpandTableColumn(#"Added Index", "Invoice", {"Column1", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column3", "Column4", "Column5", "Column6"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Invoice", "LineIndex", each if [Column1] = "H" then 0
else [Column3], Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "VendorIndex", each if [Column1] = "V" then [Column4]
else 0, Int64.Type),
#"Inserted Merged Column" = Table.AddColumn(#"Added Custom2", "RowIndex", each Text.Combine({Text.From([InvoiceIndex], "en-US"), Text.From([LineIndex], "en-US"), Text.From([VendorIndex], "en-US")}, ""), type text),
#"Sorted Rows" = Table.Sort(#"Inserted Merged Column",{{"RowIndex", Order.Ascending}})
in
#"Sorted Rows"
1 Reply
I've solved it myself 🙂
Probably not the most elegant solution, but it seems to work.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Invoice", each _, type table [Column1=nullable text, Column2=nullable number, Column3=nullable number, Column4=nullable number, Column5=nullable text, Column6=nullable number]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "InvoiceIndex", 1, 1, Int64.Type),
#"Expanded Invoice" = Table.ExpandTableColumn(#"Added Index", "Invoice", {"Column1", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column3", "Column4", "Column5", "Column6"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Invoice", "LineIndex", each if [Column1] = "H" then 0
else [Column3], Int64.Type),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "VendorIndex", each if [Column1] = "V" then [Column4]
else 0, Int64.Type),
#"Inserted Merged Column" = Table.AddColumn(#"Added Custom2", "RowIndex", each Text.Combine({Text.From([InvoiceIndex], "en-US"), Text.From([LineIndex], "en-US"), Text.From([VendorIndex], "en-US")}, ""), type text),
#"Sorted Rows" = Table.Sort(#"Inserted Merged Column",{{"RowIndex", Order.Ascending}})
in
#"Sorted Rows"