Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi,
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.
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |