Diagonal matrix to table - How to transform a table with a diagonal with data to a proper table

[Ander101]

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.

[Jimmy801 · Accepted solution]

Hello @Ander101 

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

[Ander101]

Hello everyone,

Thanks for the help. I haven't used the mentioned code. I could solve the issue using the SUMMARIZE command in a new table and then adding the numbers, which was way more intuitive to me.

[v-alq-msft · Accepted solution]

Hi, @Ander101 

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.

[Jimmy801 · Accepted solution]

Hello @Ander101 

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

[AlB]

@Ander101 

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 

[AlB]

@Ander101 

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 

[Ander101]

@AlB I tried to paste the table but it said it was not compatible with HTML so I tried to convert it to HTML.

Anyway, here is a link to the excel file with the table. LINK

[AlB]

H @Ander101 

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 

[Ander101]

@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]

[Greg_Deckler]

@Ander101 - Seems like in Power Query you could select your columns in turn and filter out the blanks?

[Ander101]

@Greg_Deckler Can you develop a little bit more? If I filter out the blanks, I end up deleting data from other columns.