Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AllanXu
Helper II
Helper II

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

 

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,

2 ACCEPTED SOLUTIONS
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
MFelix
Super User
Super User

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.