How to do the opposite of GROUP BY (or UNGROUP BY) in Power BI?

[AllanXu]

I have a dataset that includes summarized records. That means instead of repeating records like below:

Column 1	Column 2
A	X
A	X
A	X
A	Y
B	Y
B	Y

The dataset includes the following rows:

Column 1	Column 2	Number_of_rows
A	X	3
A	Y	1
B	Y	2

While the summarized format significantly reduces the size of the dataset, it imposes challenges in a few areas of Power BI.

Challenge#1: The Key Influencer in PowerBI uses the “Number_of_rows” column as a normal number data (like age or price) and it does not know that the “Number_of_rows” means the row repeats n times.

Challenge #2: I am not able to find a way to tell SandDance that the “Number_of_rows” column is a count column. It sees the column as a normal number column like age.

What are my options to teach PowerBI that “Number_of_rows” column is a count and it means the row repeats n number of times?

Thank you,

[MFelix · Accepted solution]

Hi @AllanXu ,

Check the video below that makes use of the Table.Repeat function.

Believe is what you are looking for see below the code with your table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYmOlWB0ILxKIDcE8JyjPSCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Number_of_rows = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Number_of_rows", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Repeat(Table.FromRecords({[Col1 =1]}),[Number_of_rows])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Col1"}, {"Col1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Col1", "Number_of_rows"})
in
    #"Removed Columns"

Regards,

MFelix

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column 1", type text}, {"Column 2", type text}, {"Number_of_rows", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {Number.From(1)..Number.From([Number_of_rows])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Number_of_rows", "Custom"})
in
    #"Removed Columns"

Hope this helps.

[MFelix · Accepted solution]

Hi @AllanXu ,

Check the video below that makes use of the Table.Repeat function.

Believe is what you are looking for see below the code with your table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUYoAYmOlWB0ILxKIDcE8JyjPSCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Number_of_rows = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Number_of_rows", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.Repeat(Table.FromRecords({[Col1 =1]}),[Number_of_rows])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Col1"}, {"Col1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Col1", "Number_of_rows"})
in
    #"Removed Columns"

Regards,

MFelix