Forum Discussion

Keith011's avatar
Keith011
Helper III
4 years ago
Solved

How to get data ready for unpivot?

Hi guys.

 

This is my data in power query (I'm just doing a demo in Excel)

 

This is how I want the data to be (like below). So that I could do a Unpivot Column and the data is ready to be visualized in a chart

 

 

Any idea?

 

Thanks in advance

Need Help

8 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dYs9CoAwDEavEuIqgnoPV4fSIdraFvoDteL1DUXq5Pa9vDwhsMMexwEWChoI9uRTRtkLHPmetWp785eu8AarpQLuhGK5iy6Qr3Ziu1NpWyXzV53OxOpmdl4fpUF2xn6k0s1/8gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column1]="#" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "#")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"AllRows", each Table.AddIndexColumn(_,"Index",0,1)}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Column2", "Index"}, {"Column2", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded AllRows", List.Distinct(#"Expanded AllRows"[Custom]), "Custom", "Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

     👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

    How to get your questions answered quickly -- How to provide sample data

  • Keith011's avatar
    Keith011
    Helper III
    4 years ago

    Hi Vijay_A_Verma 

     

    Thanks for getting back!

     

    Why does it says Column1 wasn't found in the 3rd step? I checked, the column1 actually exists in the dataset

     

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Can you check if Column1 name of table doesn't contain any blank before or after the word? Most possible reason is this.

      • Keith011's avatar
        Keith011
        Helper III
        4 years ago

        Still doesn't work. Vijay_A_Verma 

         

        I checked the column name "Column1" there is no space before or after.

         

        I changed the column name to "Number" and changed 3rd step from Column 1 to "Number" , still doesn't work.

         

  • Keith011's avatar
    Keith011
    Helper III
    4 years ago
    let
    Source = Excel.Workbook(File.Contents("C:\Users\abc\OneDrive - abc Pte. Ltd\Desktop\Webinar Reports\Test (Automate All In PBI)\Nigel Data\New folder\AZ Commune\Test (Regional Template) v3 - az.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [Column1]="#" then [Column2] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "#")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"AllRows", each Table.AddIndexColumn(_,"Index",0,1)}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Column2", "Index"}, {"Column2", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded AllRows", List.Distinct(#"Expanded AllRows"[Custom]), "Custom", "Column2"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns1"

    above is my M Code

    Might there be something wrong with the Source part?