Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
Hmm, this seems like a job for @ImkeF
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
Thanks Zubair_Muhammad! That's it!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |