GENERATESERIES OVER A GROUP in POWER BI using DAX?

[Anonymous]

Hi,

I have been struggling to understand how to create or transform data from and to (below)-

From-

UniqueID	MinumumVal	MaximumVal	Product
1	1	4	A
2	5	7	B
3	8	11	C
4	12	15	D

TO -

UniqueID	Val	Product
1	1	A
1	2	A
1	3	A
1	4	A
2	5	B
2	6	B
2	7	B
3	8	C
3	9	C
3	10	C
3	11	C
4	12	D
4	13	D
4	14	D
4	15	D

I tried using GenerateSeries but not sure how to loop though and group over UniqueIDs?

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this code on Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMTIHZUitWJVjICskyB2ByIncAixkCWBUgdSKEzWAik3BCk0hCk1EUpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UniqueID = _t, MinumumVal = _t, MaximumVal = _t, Product = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}, {"MinumumVal", Int64.Type}, {"MaximumVal", Int64.Type}, {"Product", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Diff", each [MaximumVal] - [MinumumVal]),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Diff", type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Diff"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Generate", each {[MinumumVal]..[MaximumVal]}),
#"Expanded Generate" = Table.ExpandListColumn(#"Added Custom1", "Generate"),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Generate",{"MinumumVal", "MaximumVal"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"UniqueID", "Generate", "Product"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Generate", "Val"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Val", type number}})
in
#"Changed Type2"

Or you can download this example: Sample PBIX 

Ricardo