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 and Unpivot I can think of, but I can't seem to get it to work.... hopefully someone cleverer than me can help please?

 

Thank you

 

Michael

 

 

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

3 Replies

  • 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

  • MichaelF1's avatar
    MichaelF1
    Icon for Helper III rankHelper III
    4 years ago

    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

     

    • MichaelF1's avatar
      MichaelF1
      Icon for Helper III rankHelper III
      4 years ago

      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