splitting and unpivoting two columns

[Pete_Murphy_71]

HI, I am looking to how to unsplit and unpivot some data but having a hard time of it.

Here is an example of what I have:

Year	Value	Amount
2020	1;5;8	10;20;40
2021	2;6	50;70
2022	1;4;7;3	50;60;80;90

This below is basicially what I am trying to get to

Year	Value	Amount
2020	1	10
2020	5	20
2020	8	40
2021	2	50
2021	6	70
2022	1	50
2022	4	60
2022	7	80
2022	3	90

Can anyone in the community give a brief explanation of how I would do this.

Right now I am trying to first Split the Value and Amount Columns and try to unpivot, however I am just not getting to the result I am looking for.... Can anyone help?

Many thanks in advance...

[v-yueyunzh-msft · Accepted solution]

Hi , @Pete_Murphy_71 

Very glad you solved the problem, here are my tests and results for this issue:

We can put this M code in "Advanced Editor" :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYvJDcAgEAN72TcPs+GKphRE/21kAxIfW5qx5zSXy5JlKuNv4aLIVtouB3NaZBX9Yt+XQuc5qokh3hisDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Value = _t, Amount = _t]),
    test = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Value", type text}, {"Amount", type text}}),
    Custom1 = Table.TransformColumns(test,{{"Value",(x)=> Text.Split(x,";") },{"Amount",(x)=>Text.Split(x,";")}}      ),
    #"Added Custom" = Table.AddColumn(Custom1, "Custom", each Table.FromColumns({[Value],[Amount]})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Amount"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Column1", "Value"}, {"Column2", "Amount"}})
in
    #"Renamed Columns"

And then we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[Pete_Murphy_71]

I think I can answer my own question now (I worked on this a little bit) 😁

Basically,

1. Transpose the data
2. Split the columns by the semi-column
3. Transpose the data back
4. Use Fill Down
5. Rename the columns and changed the data type...

Thanks to anyone who did read this though... glad I figured it out...