Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Sorry for the strange title. I have a dataset formed by answers of people in a fom, and it looks like this:
So, I want to merge all those rows with the same week number and delete those empty cells so that it looks like this:
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.
Solved! Go to 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
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.
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.
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.
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
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
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
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]
@Ander101 - 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.