CODEWITHSUNDEEP
excelpowerbipowerqueryunpivotm
Grandizer
Grandizer

Reputation: 3025

Power BI - Take Multiple Column Pairs of Year and Value Columns and Merge into just 2 Columns

I am trying to get my data in a more usable format. I have the following columns.

Donor     2019 Date  2019 Amt  2018 Date  2018 Amt  2017 Date  2017 Amt
--------  ---------  --------  ---------  --------  ---------  --------
Person 1  1/15/2019    100.00  4/20/2018     75.00  NULL           0.00 
Person 2  NULL           0.00  7/15/2018     50.00  NULL           0.00
Person 3  2/21/2019     50.00  3/03/2018     50.00  2/28/2017     50.00

This data actually goes back to 2010.

What I want to get to is:

Donor     Date       Amt
--------  ---------  ------
Person 1  1/15/2019  100.00
Person 1  4/20/2018   75.00
Person 2  7/15/2018   50.00
Person 3  2/21/2019   50.00
Person 3  3/03/2018   50.00
Person 3  3/28/2017   50.00

I have played around with some Unpivoting of data but nothing I have tired is getting me exactly what I want. I assume it may take a few transformations to get it exactly to what I need.

Upvotes: 1

Views: 135

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

You can do it with two unpivots and a filter:

Select all the year date columns and unpivot, then select all the year amount columns and unpivot. Finally, filter down to rows where these years match.

Full M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEy1Dc01TcyMLQEsQ0MgKQJkAsSsQCyzU2BBBAZKMXqwLUZwcSA8lDdILWmBljUGgMFjPSNDGFWgBUZ6xujaAIqsAAJmEMEYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Donor = _t, #"2019 Date" = _t, #"2019 Amt" = _t, #"2018 Date" = _t, #"2018 Amt" = _t, #"2017 Date" = _t, #"2017 Amt" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Donor", type text}, {"2019 Date", type date}, {"2019 Amt", Int64.Type}, {"2018 Date", type date}, {"2018 Amt", Int64.Type}, {"2017 Date", type date}, {"2017 Amt", Int64.Type}}),
    #"Unpivoted Year Date" = Table.Unpivot(#"Changed Type", {"2019 Date", "2018 Date", "2017 Date"}, "Year Date", "Date"),
    #"Unpivoted Year Amt" = Table.Unpivot(#"Unpivoted Year Date", {"2019 Amt", "2018 Amt", "2017 Amt"}, "Year Amt", "Amt"),
    #"Match Years Filter" = Table.SelectRows(#"Unpivoted Year Amt", each Text.Start([Year Date], 4) = Text.Start([Year Amt], 4))
in
    #"Match Years Filter"

This solution should be more convenient if you have many columns.

With a bit of M code magic using Table.ColumnNames you can make it fully dynamic like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PUzBU0lEy1Dc01TcyMLQEsQ0MgKQJkAsSsQCyzU2BBBAZKMXqwLUZwcSA8lDdILWmBljUGgMFjPSNDGFWgBUZ6xujaAIqsAAJmEMEYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Donor = _t, #"2019 Date" = _t, #"2019 Amt" = _t, #"2018 Date" = _t, #"2018 Amt" = _t, #"2017 Date" = _t, #"2017 Amt" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Donor", type text}, {"2019 Date", type date}, {"2019 Amt", Int64.Type}, {"2018 Date", type date}, {"2018 Amt", Int64.Type}, {"2017 Date", type date}, {"2017 Amt", Int64.Type}}),
    #"Unpivoted Year Date" = Table.Unpivot(#"Changed Type", List.Select(Table.ColumnNames(#"Changed Type"), each Text.EndsWith(_, " Date")), "Year Date", "Date"),
    #"Unpivoted Year Amt" = Table.Unpivot(#"Unpivoted Year Date", List.Select(Table.ColumnNames(#"Changed Type"), each Text.EndsWith(_, " Amt")), "Year Amt", "Amt"),
    #"Match Years Filter" = Table.SelectRows(#"Unpivoted Year Amt", each Text.Start([Year Date], 4) = Text.Start([Year Amt], 4))
in
    #"Match Years Filter"

This part

List.Select(Table.ColumnNames(#"Changed Type"), each Text.EndsWith(_, " Amt"))

takes all the column names and picks out the ones ending in " Amt".

Upvotes: 1

Aldert
Aldert

Reputation: 4323

you can use the following m-query

let
    Source = Csv.Document(File.Contents("C:\....\Documents\Pivot.csv"),[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Donor", type text}, {"2019 Date", type text}, {"2019 Amt", type number}, {"2018 Date", type text}, {"2018 Amt", type number}, {"2017 Date", type text}, {"2017 Amt", type number}}),
    #"Data2019" = Table.SelectColumns(#"Changed Type",{"Donor", "2019 Date", "2019 Amt"}),
    #"Renamed2019" = Table.RenameColumns(#"Data2019",{{"2019 Date", "Date"}, {"2019 Amt", "Value"}}),
    #"Data2018" = Table.SelectColumns(#"Changed Type",{"Donor", "2018 Date", "2018 Amt"}),
    #"Renamed2018" = Table.RenameColumns(#"Data2018",{{"2018 Date", "Date"}, {"2018 Amt", "Value"}}),
    #"Data2017" = Table.SelectColumns(#"Changed Type",{"Donor", "2017 Date", "2017 Amt"}),
    #"Renamed2017" = Table.RenameColumns(#"Data2017",{{"2017 Date", "Date"}, {"2017 Amt", "Value"}}),
    #"UnionAll" = Table.Combine({#"Renamed2017", #"Renamed2018", #"Renamed2019"}),
    #"Filtered Rows" = Table.SelectRows(UnionAll, each ([Date] <> "NULL"))
in
    #"Filtered Rows"

It will select the columns into 3 different tables after which I combine them together. It is not dynamic meaning that when you have an extra year, it will not adjust accordingly.

Upvotes: 1

Related Questions