Forum Discussion
Converting a Table
Hello everyone,
i am strungling with a problem and i hope that somebody is able to help.
I am trying to add a new table to my dataset by dublicating a table in query editor or by using dax formulars.
The basic situation is following: A project is split up into several subsections but not every project contains every subsection.
I did a query from a folder which results in a table like this
| Project | Subsection |
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | B |
| 2 | C |
| 2 | D |
The result should give me a column for every Project and mark (1 or 0/True or False etc.) if the subsection is needed in this project.
| Subsection | Project 1 | Project 2 |
| A | 1 | 0 |
| B | 1 | 1 |
| C | 1 | 1 |
| D | 0 | 1 |
I tried different transformations in query editor and serveral dax-formulars but in every possible solution i found there is always one missing part to complete the puzzle.
Maybe someone of you is able to help me.
Best Regards
Florian
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.
5 Replies
FloNi1902 maybe add a below measure and then use a matrix visual , subsection on rows, project on columns and below measure on values:
Count = COUNTROWS ( Table ) + 0✨ 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.
Hello parry2k,
thank you for solution. I think I forgot to mention that i do not want to visualize this tables. Instead I am trying to create a new table which i want to add to my dataset.
I will add this to my post.
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!
FloNi1902 Glad it worked. Cheers!!
✨ 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.
