How to get data ready for unpivot?

[Keith011]

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

[Keith011]

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? 

[Vijay_A_Verma]

Please give me your Excel and remove all data. I just need headers to check and make your code work.

How to provide sample data

[Keith011]

here you go. just ignore the User Name, Email and Submitted Date column, it's just there from the raw file

@Vijay_A_Verma 

Book1.xlsx

[Vijay_A_Verma · Accepted solution]

Here it is alongwith M code

[Keith011]

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]

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

[Keith011]

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.

[Vijay_A_Verma]

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