Forum Discussion

MichaelF1's avatar
MichaelF1
Icon for Helper III rankHelper III
4 years ago
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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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