Forum Discussion
Solved
Power Query: Confused with Pivot and Unpivot!
Hi All, I am working with data in Power Query. At the oment I have data lodaed into PQ in the form: I need to get it into the form: I've tried pretty much every combination of Pivot ...
MichaelF1 , Try this code in empty query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWBsIzALCcgyxjOMgGznIEsUzjLDMxyAbLM4SwLpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Number.Mod([Column2],2)), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Column2", List.Sum), #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "Col2"}, {"0", "Col3"}}) in #"Renamed Columns"Only possible as values in column 2 is splitter odd and even
Hi amitchandak , thanks for your suggestion. I can replicate your code and it runs correctly, but I cannot then apply it to the real example I have.
When I run the code, step by step, I can get to the #"Added Custom" step when you add Column2. However, the 1s and 0s do not match how it was in your example and the subsequent steps don't work.
This what I get after the #"Added Custom"
Thanks again,
Michael
Hi again amitchandak ,
It seems to be working now! I put and index column in, then used the Number.Mod method to get the 1,0,10, column, then the rest of you code worked, so thank you for you help 🙂
Michael