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 guys,
Current situation - I have this input table:
Desired situation -
So the idea is that power query transforms the input table:
1) gets the product from product column
2) creates the date column, where the first date is [From] and the last is [To] - and between these dates there are consecutive days
3) each unique combination of [product + price + from + to] appends to each other - oucome is the desired table.
Do you guys think it is possible to perform that transformation in power query?
Solved! Go to Solution.
It is quite easy if you convert the dates to numbers, add a column with nested lists with all numbers representing the from..to dates,
remove the From/To columns, expand the new column and adjust the data type to date.
let Source = InputTable, #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", Int64.Type}, {"To", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {[From]..[To]}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"From", "To"}), #"Expanded Date" = Table.ExpandListColumn(#"Removed Columns", "Date"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}) in #"Changed Type1"
It is quite easy if you convert the dates to numbers, add a column with nested lists with all numbers representing the from..to dates,
remove the From/To columns, expand the new column and adjust the data type to date.
let Source = InputTable, #"Changed Type" = Table.TransformColumnTypes(Source,{{"From", Int64.Type}, {"To", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {[From]..[To]}), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"From", "To"}), #"Expanded Date" = Table.ExpandListColumn(#"Removed Columns", "Date"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}) in #"Changed Type1"
Brilliant, thanks kind sir!
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |