Forum Discussion
Converting a Table
FloNi1902 you can pivot it in PQ, start a new blank query in PQ, click advanced editor and paste the following code, from here you can apply these steps in your original data.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzgLGcwywguZoQi5qIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Subsection = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}, {"Subsection", type text}}), #"Added Custom1" = Table.AddColumn(#"Changed Type", "Project Name", each "Project " & Number.ToText([Project]), type text), #"Added Custom" = Table.AddColumn(#"Added Custom1", "Value", each 1), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Project"}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Project Name", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Project Name", type text}}, "en-CA")[Project Name]), "Project Name", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Project 1", "Project 2"}) in #"Replaced Value"✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.
FloNi1902 you can pivot it in PQ, start a new blank query in PQ, click advanced editor and paste the following code, from here you can apply these steps in your original data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWBsJzgLGcwywguZoQi5qIUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Subsection = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}, {"Subsection", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Project Name", each "Project " & Number.ToText([Project]), type text),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Value", each 1),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Project"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Project Name", type text}}, "en-CA"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Project Name", type text}}, "en-CA")[Project Name]), "Project Name", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Project 1", "Project 2"})
in
#"Replaced Value"
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.
Thank you parry,
i tried the same thing before. The step by adding the column value was the one that has been missing. Thank you very much!