Insert rows by transforming two columns

[zvm]

Hi there,

I have an input table like this:

Group	Product	From	To	Price
Gr1	Pr1	7	9	10
Gr1	Pr1	10	12	15
Gr1	Pr1	13	16	20

I would like to transform From and To into single column, but with a values between those two as well. Something like this should be the output:

Group	Product	Number	Price
Gr1	Pr1	7	10
Gr1	Pr1	8	10
Gr1	Pr1	9	10
Gr1	Pr1	10	15
Gr1	Pr1	11	15
Gr1	Pr1	12	15
Gr1	Pr1	13	20
Gr1	Pr1	14	20
Gr1	Pr1	15	20
Gr1	Pr1	16	20

Solution can be in Power Query (M code) or DAX. 

I need to perform join with another table via combined columns Group, Product and Number (in another table I have Number).

So exact join in SQL sintax would be:

T1.Group = T2.Group 

and T1.Product = T2.Product

and T1.Number between T2.From and T2.To

I would like to avoid "between" and to have T1.Number = T2.Number instead.

Then in DAX I can create relationship concatenating those 3 columns.

Thank you in advance!

[Greg_Deckler]

Hmm, this seems like a job for @ImkeF

[Zubair_Muhammad · Accepted solution]

@zvm

This shall do it

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci8yVNJRCgCT5kBsCcSGBkqxOqhSQCEgYQQiTDEljUGEGZAwAuqMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Product = _t, From = _t, To = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group", type text}, {"Product", type text}, {"From", Int64.Type}, {"To", Int64.Type}, {"Price", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Number", each {[From]..[To]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Number"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"From", "To"})
in
    #"Removed Columns"

 Basically add a new custom column and expand it

[zvm]

Thanks Zubair_Muhammad! That's it!