Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Multiple unpivot in one table

Hi ,   how to unpivot Multiple column ,  multiple unpivot in one table   see the below image i have a data and result like this     How to solve this?   Thanks  Rajesh  
Need Help
Tips and Tricks
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hello, @RajeshRanganath, you may want to use the following code in Power Query to achieve your goal.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJNLEnOANLuSGxnODtWJ1rJCcjzy1eASXqgcl2QuSDlzhCB4PzSouRUINsTje+KwgfpcEGy2gvVdG8Ul7jiktRR8kFR6IZmpw+aDVCVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Comments A" = _t, #"Column B" = _t, #"Comments B" = _t, #"Column C" = _t, #"Comments C" = _t]),
    Columns = List.Zip(List.Split(Table.ColumnNames(Source), 2)){0},
    Rows = Table.RowCount(Source),
    #"Reformed Table" = Table.Combine(List.Transform(List.Split(Table.ToColumns(Source),2), each Table.FromColumns(_))),
    #"Added Index" = Table.AddIndexColumn(#"Reformed Table", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Columns{Number.IntegerDivide(_, Rows)}}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Column2]), "Column2", "Column1", List.Count)
in
    #"Pivoted Column"

    Screenshot 2021-01-03 202433.png

  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hello

    You can download my PBI file from here.

    I hope this helps.

    Untitled.png

parry2k's avatar
parry2k
Super User
5 years ago

Anonymous start a blank query and paste this m code  in advanced editor, it will create a table.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJNLEnOANLuSGxnODtWJ1rJCcjzy1eASXqgcl2QuSDlzhCB4PzSouRUINsTje+KwgfpcEGy2gvVdG8Ul7jiktRR8kFR6IZmpw+aDVCVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col A" = _t, #"Comments A" = _t, #"Col B" = _t, #"Comments B" = _t, #"Col C" = _t, #"Comments C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col A", type text}, {"Comments A", type text}, {"Col B", type text}, {"Comments B", type text}, {"Col C", type text}, {"Comments C", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Col A", "Col B", "Col C"}, "Field", "Value")
in
    #"Unpivoted Columns"

 

Use matrix visual put Field on Rows, Value on columns, and count on values, and you will get the result

 

 

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡