Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
alex_ama
New Member

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.

 

alex_ama_0-1687835246391.png

 

 

 

1 ACCEPTED SOLUTION
alex_ama
New Member

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"

 

alex_ama_0-1687836195057.png

 

 

View solution in original post

1 REPLY 1
alex_ama
New Member

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"

 

alex_ama_0-1687836195057.png

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors