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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
siskays
Regular Visitor

Modify multiple row into column with different value

Hi all, I have this data table on Power Query:

Account A1000
Charges1500
Discount 1-400
Discount 2-200
Tax100
Account B2000
Charges3000
Discount 1-1500
Tax500

 

And I want to change above table into this:

Account NameFinal ChargesChargesDiscount 1Discount 2Tax
Account A10001500-400-200100
Account B20003000-15000500

 

Final Charges is the total of Charges, Discount 1, Discount 2, and Tax.

 

How do I change the previous table format to the table format I want in Power Query? Thank you!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@siskays Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1FwVNJRMjQwMFCK1YlWcs5ILEpPLQYJmUKFXDKLIQoNgaK6JuiiRiBRI6hoSGIFxDQwD2aDE1DMCNMGYwOsNsAthhgG5sUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}, {"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column], "Account") then [Column] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Custom]), "Custom", "Column1", List.Sum),
    #"Transposed Table" = Table.Transpose(#"Pivoted Column"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account A", Int64.Type}, {"Account B", Int64.Type}, {"Charges", Int64.Type}, {"Discount 1", Int64.Type}, {"Discount 2", Int64.Type}, {"Tax", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Charges", "Discount 1", "Discount 2", "Tax"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Account Name"}, {"Value", "Final"}})
in
    #"Renamed Columns"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
wdx223_Daniel
Super User
Super User

=Table.Combine(Table.Group(PreviousStepName,"Column1",{"n",each let a=Table.ToColumns(_) in #table({"Account Name","Final Charges"}&List.Skip(a{0}),{{a{0}{0}}&a{1}})},0,(x,y)=>Byte.From(Text.StartsWith(y,"Account")))[n])

Greg_Deckler
Super User
Super User

@siskays Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1FwVNJRMjQwMFCK1YlWcs5ILEpPLQYJmUKFXDKLIQoNgaK6JuiiRiBRI6hoSGIFxDQwD2aDE1DMCNMGYwOsNsAthhgG5sUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column", type text}, {"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column], "Account") then [Column] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[Custom]), "Custom", "Column1", List.Sum),
    #"Transposed Table" = Table.Transpose(#"Pivoted Column"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account A", Int64.Type}, {"Account B", Int64.Type}, {"Charges", Int64.Type}, {"Discount 1", Int64.Type}, {"Discount 2", Int64.Type}, {"Tax", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Charges", "Discount 1", "Discount 2", "Tax"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Account Name"}, {"Value", "Final"}})
in
    #"Renamed Columns"


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors