Convert rows into column based on 3 unique columns

Hi danialsj ,

 

You need to do the following:

• Delete the column savings
• Select column Currency
• Pivot - Based on Current
• Add a new step refering to the step before you deleted the savings column
• Delete the column current
• Select column Currency
• Pivot - Based on Current
• Merge the two steps where you have pivot based on date and branch.
• Expand the Savgins columns

Check code and attach PBIX.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEyBWKf/OTEHCBtaGAAJWN1MBS55RelZqbngRSYgsSM4MqMsJllBDbLyASrIiSzcFlphukuU6yKkMwyxOEuZLNM0Cw0wm6WsQHcrFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, BRANCH = _t, CURRENCY = _t, CURRENT = _t, SAVINGS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"BRANCH", Int64.Type}, {"CURRENCY", type text}, {"CURRENT", Int64.Type}, {"SAVINGS", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"SAVINGS"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[CURRENCY]), "CURRENCY", "CURRENT", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Local", "Current_Local"}, {"Foreign", "Current_Foreign"}}),
    Custom1 = #"Changed Type",
    #"Removed Columns1" = Table.RemoveColumns(Custom1,{"CURRENT"}),
    #"Pivoted Column1" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[CURRENCY]), "CURRENCY", "SAVINGS", List.Sum),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column1",{{"Local", "Savings_Local"}, {"Foreign", "Savings_Foreign"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"BRANCH", "DATE"}, #"Renamed Columns1", {"BRANCH", "DATE"}, "Pivoted Column1", JoinKind.LeftOuter),
    #"Expanded Pivoted Column1" = Table.ExpandTableColumn(#"Merged Queries", "Pivoted Column1", {"Savings_Local", "Savings_Foreign"}, {"Savings_Local", "Savings_Foreign"})
in
    #"Expanded Pivoted Column1"

 

Hi danialsj , 

You could design it in Matrix like below, or try to use M code to change its structure

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lEyBWKf/OTEHCBtaGAAJWN1MBS55RelZqbngRSYgsSM4MqMsJllBDbLyASrIiSzcFlphukuU6yKkMwyxOEuZLNM0Cw0wm6WsQHcrFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DATE = _t, BRANCH = _t, CURRENCY = _t, CURRENT = _t, SAVINGS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"BRANCH", Int64.Type}, {"CURRENCY", type text}, {"CURRENT", Int64.Type}, {"SAVINGS", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"CURRENT", type text}, {"SAVINGS", type text}}, "en-US"),{"CURRENT", "SAVINGS"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[CURRENCY]), "CURRENCY", "Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Local", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Local.1", "Local.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Local.1", Int64.Type}, {"Local.2", Int64.Type}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Foreign", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Foreign.1", "Foreign.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Foreign.1", Int64.Type}, {"Foreign.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Local.1", "LOCAL_CURRENT"}, {"Local.2", "LOCAL_Saving"}, {"Foreign.1", "Foreign_CURRENT"}, {"Foreign.2", "Foreign_Saving"}})
in
    #"Renamed Columns"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.