Convert rows into column based on 3 unique columns

[danialsj]

I'm trying to reduce the number of rows in my data by creating new columns based on the existing columns. 

My dataset looks like this:

DATE	BRANCH	CURRENCY	CURRENT	SAVINGS
1/1/2020	5	Local	100	100
1/1/2020	5	Foreign	150	200
1/2/2020	5	Local	200	240
1/2/2020	5	Foreign	100	100
1/1/2020	6	Local	100	150
1/1/2020	6	Foreign	110	200
1/2/2020	6	Local	400	100
1/2/2020	6	Foreign	300	200

I want to convert the Currency_Type column into separate columns (e.g. LOCAL_CURRENT and FOREIGN_CURRENT instead of two separate rows for each). My dataset should turn out like this:

DATE	BRANCH	LOCAL_CURRENT	LOCAL_SAVINGS	FOREIGN_CURRENT	FOREIGN_SAVINGS
1/1/2020	5	100	100	150	200
1/2/2020	5	200	240	100	100
1/1/2020	6	100	150	110	200
1/2/2020	6	400	100	300	200

Each row should has a date and branch number as a unique record. So branch 5 on 1/1/2020 will have one row.

How do I do this in Query editor?

[MFelix · Accepted solution]

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"

[dax · Accepted solution]

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.

[MFelix · Accepted solution]

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"

[amitchandak]

@danialsj , Try pivot

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/