Forum Discussion
Diagonal matrix to table - How to transform a table with a diagonal with data to a proper table
Sorry for the strange title. I have a dataset formed by answers of people in a fom, and it looks like this:
Original dataset
So, I want to merge all those rows with the same week number and delete those empty cells so that it looks like this:
Goal dataset
Is there any way to do this? Please take into accound that every week we have more data added.
What I tried by now:
By now I have already tried to transpose the matrix, merge the columns and then transpose again, but then I have the week numbers added together and I don't think that the query can know that I wan to merge groups of 5 columns afterwards.
I also tried to create new tables using SELECTCOLUMNS, but then I wont be able to append the tables because tables created from DAX formulas do not appear in Power Query.
Hello Anonymous
you can use some Table.Group and apply a function there that does the work.Consider that this code would combine the cell content if it would find 2 cells for one person in a week.
Text.Combine(List.Transform(_, each Text.From(_)),",")in case you could adapt the above code. Here a practicable example.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrFQ0lEyBGIQitWBCgCREboAEBlDBCyBTBNkLZYQjim6ABCZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [week = _t, Tom = _t, Jelena = _t, Maria = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"week", Int64.Type}, {"Tom", Int64.Type}, {"Jelena", Int64.Type}, {"Maria", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"week"}, {{"AllRows", each Table.FromColumns(List.Transform(Table.ToColumns(Table.RemoveColumns(_, {"week"})), each {Text.Combine(List.Transform(_, each Text.From(_)),",")}), Table.ColumnNames(Table.RemoveColumns(_, {"week"})) )}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", Table.ColumnNames(Table.Combine(#"Grouped Rows"[AllRows]))) in #"Expanded AllRows"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
JimmyHi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may add a new step as below.
= Table.Group(#"Changed Type", {"Week"}, {{"Tom", each List.Sum([Tom]), type nullable number}, {"Alice", each List.Sum([Alice]), type nullable number}, {"John", each List.Sum([John]), type nullable number}, {"Judd", each List.Sum([Judd]), type nullable number}, {"Thomas", each List.Sum([Thomas]), type nullable number}})Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
Jimmy801Community Champion
Hello Anonymous
you can use some Table.Group and apply a function there that does the work.Consider that this code would combine the cell content if it would find 2 cells for one person in a week.
Text.Combine(List.Transform(_, each Text.From(_)),",")in case you could adapt the above code. Here a practicable example.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrFQ0lEyBGIQitWBCgCREboAEBlDBCyBTBNkLZYQjim6ABCZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [week = _t, Tom = _t, Jelena = _t, Maria = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"week", Int64.Type}, {"Tom", Int64.Type}, {"Jelena", Int64.Type}, {"Maria", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"week"}, {{"AllRows", each Table.FromColumns(List.Transform(Table.ToColumns(Table.RemoveColumns(_, {"week"})), each {Text.Combine(List.Transform(_, each Text.From(_)),",")}), Table.ColumnNames(Table.RemoveColumns(_, {"week"})) )}}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", Table.ColumnNames(Table.Combine(#"Grouped Rows"[AllRows]))) in #"Expanded AllRows"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy- Greg_Deckler
Anonymous - Seems like in Power Query you could select your columns in turn and filter out the blanks?
Greg_Deckler Can you develop a little bit more? If I filter out the blanks, I end up deleting data from other columns.
- AlB
H Anonymous
Can you show the tables (particularly the initial one) in text-tabular format so that the contents can be copied? Rather than on a screen cap
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB There you go. It took a little bit more time but it is ready:
[table]
[tr]
[th]Week[/th]
[th]Tom[/th]
[th]Alice[/th]
[th]John[/th]
[th]Judd[/th]
[th]Thomas[/th]
[/tr]
[tr]
[td]48[/td]
[td]1[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]48[/td]
[td]null[/td]
[td]2[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]48[/td]
[td]null[/td]
[td]null[/td]
[td]3[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]48[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]1[/td]
[td]null[/td]
[/tr]
[tr]
[td]48[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]1[/td]
[/tr]
[tr]
[td]49[/td]
[td]3[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]49[/td]
[td]null[/td]
[td]3[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]49[/td]
[td]null[/td]
[td]null[/td]
[td]1[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]49[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]2[/td]
[td]null[/td]
[/tr]
[tr]
[td]49[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]3[/td]
[/tr]
[tr]
[td]50[/td]
[td]1[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]50[/td]
[td]null[/td]
[td]3[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]50[/td]
[td]null[/td]
[td]null[/td]
[td]2[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]50[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]2[/td]
[td]null[/td]
[/tr]
[tr]
[td]50[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]2[/td]
[/tr]
[tr]
[td]51[/td]
[td]3[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]51[/td]
[td]null[/td]
[td]2[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]51[/td]
[td]null[/td]
[td]null[/td]
[td]1[/td]
[td]null[/td]
[td]null[/td]
[/tr]
[tr]
[td]51[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]3[/td]
[td]null[/td]
[/tr]
[tr]
[td]51[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]null[/td]
[td]2[/td]
[/tr]
[/table]
- AlB
Anonymous
What is this? How do I get that into a table?
I was talking of something like:
Tom Alice 48 1 23 2 which is what you get by simply pasting a table (copied from excel or PBI) here. Otherwise share the table in an .xlsx or. pbix You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- AlB
Anonymous
Place the following M code in a blank query to see the steps. #"Grouped Rows" is the crucial one
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrFQ0lHKK83JwU7F6kCVGBGQhwoaE5CHUoYE5OHKwPKWhJ1oicVudHl0u3HIQykjAvJwD4PkTQ0IOhGsBI8TkY1AD2wcVoCUEbAe6jx0P2Mx3hB/JCMbgS0EsVhhTEAe7ovYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Alice = _t, John = _t, Judd = _t, Thomas = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Alice", Int64.Type}, {"John", Int64.Type}, {"Judd", Int64.Type}, {"Thomas", Int64.Type}, {"Week", Int64.Type}}), colNames_ = Table.ColumnNames(#"Changed Type"), tableType_ = Value.Type(#"Changed Type"), #"Grouped Rows" = Table.Group(#"Changed Type", {"Week"}, {{"Res", each Table.FromColumns(List.Transform(Table.ToColumns(_), each {List.Max(_)}), colNames_) }}), #"Expanded Res" = Table.ExpandTableColumn(#"Grouped Rows", "Res", {"Alice", "John", "Judd", "Thomas"}, {"Alice", "John", "Judd", "Thomas"}), final_ = Value.ReplaceType(#"Expanded Res", tableType_) in final_Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
v-alq-msftCommunity Support
Hi, Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may add a new step as below.
= Table.Group(#"Changed Type", {"Week"}, {{"Tom", each List.Sum([Tom]), type nullable number}, {"Alice", each List.Sum([Alice]), type nullable number}, {"John", each List.Sum([John]), type nullable number}, {"Judd", each List.Sum([Judd]), type nullable number}, {"Thomas", each List.Sum([Thomas]), type nullable number}})Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
