cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zvm Regular Visitor
Regular Visitor

Insert rows by transforming two columns

Hi there,

 

I have an input table like this:

 

GroupProductFromToPrice
Gr1Pr17910
Gr1Pr1101215
Gr1Pr1131620

 

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:

 

GroupProductNumberPrice
Gr1Pr1710
Gr1Pr1810
Gr1Pr1910
Gr1Pr11015
Gr1Pr11115
Gr1Pr11215
Gr1Pr11320
Gr1Pr11420
Gr1Pr11520
Gr1Pr11620

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Insert rows by transforming two columns

@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

 

insert.png

3 REPLIES 3
Super User
Super User

Re: Insert rows by transforming two columns

Hmm, this seems like a job for @ImkeF


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

Proud to be a Datanaut!


Super User
Super User

Re: Insert rows by transforming two columns

@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

 

insert.png

Highlighted
zvm Regular Visitor
Regular Visitor

Re: Insert rows by transforming two columns

Thanks Zubair_Muhammad! That's it!