The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to use powerQuery to build a table of costing information that I can use with a common date table.
I have a table of quotes, with start and end dates.
I also have two parameter in powerQuery, StartOfRange and EndOfRange
Basically, i want to build a table of the cost of each product per day, between StartOfRange and EndOfRange. Where quote information does not exist, the closest known value persists back or forwrd in time up to the StartOfRange and EndOfRange limits. This ensures that whatever date the user selects, they get a numerical value of cost for an item.
Example data:
Quote | Item | Start Date | End Date | Cost |
One | Alpha | 14/01/2020 | 22/01/2020 | 1.11 |
One | Beta | 14/01/2020 | 22/01/2020 | 2.22 |
One | Gamma | 14/01/2020 | 22/01/2020 | 3.33 |
Two | Alpha | 23/01/2020 | 27/01/2020 | 1.77 |
Two | Beta | 23/01/2020 | 27/01/2020 | 2.88 |
Desired output:
(for parameters StartOfRange=1/1/2020 and EndOfRange=30/1/2020 and only shown for one Item to illustrate the point)
Item | Quote | Date | Cost |
Alpha | 01/01/2020 | 1.11 | |
Alpha | 02/01/2020 | 1.11 | |
Alpha | 03/01/2020 | 1.11 | |
Alpha | 04/01/2020 | 1.11 | |
Alpha | 05/01/2020 | 1.11 | |
Alpha | 06/01/2020 | 1.11 | |
Alpha | 07/01/2020 | 1.11 | |
Alpha | 08/01/2020 | 1.11 | |
Alpha | 09/01/2020 | 1.11 | |
Alpha | 10/01/2020 | 1.11 | |
Alpha | 11/01/2020 | 1.11 | |
Alpha | 12/01/2020 | 1.11 | |
Alpha | 13/01/2020 | 1.11 | |
Alpha | One | 14/01/2020 | 1.11 |
Alpha | One | 15/01/2020 | 1.11 |
Alpha | One | 16/01/2020 | 1.11 |
Alpha | One | 17/01/2020 | 1.11 |
Alpha | One | 18/01/2020 | 1.11 |
Alpha | One | 19/01/2020 | 1.11 |
Alpha | One | 20/01/2020 | 1.11 |
Alpha | One | 21/01/2020 | 1.11 |
Alpha | One | 22/01/2020 | 1.11 |
Alpha | Two | 23/01/2020 | 1.77 |
Alpha | Two | 24/01/2020 | 1.77 |
Alpha | Two | 25/01/2020 | 1.77 |
Alpha | Two | 26/01/2020 | 1.77 |
Alpha | Two | 27/01/2020 | 1.77 |
Alpha | 28/01/2020 | 1.77 | |
Alpha | 29/01/2020 | 1.77 | |
Alpha | 30/01/2020 | 1.77 |
How should i approach this in M/power query?
So far, i can fill in the gaps using syntax like {[Start Date]..[End Date]} for each line in the quote table, but i am stuck adding the rows for the before and after times where there is no quote coverage
Thank you in advance!
Solved! Go to Solution.
...replying again to my own thread in case it is useful to somebody else
I extended my use case a bit to make it robust, and hence had to update my DAX
Other scenarios i wanted to cover - overlapping quotes, and gaps in quotes. The below addresses all of these now to my satisfaction.
FirmCost = // the cost strictly according to quotes - is blank if no quote is in range
VAR __StartDateOfCostToUse = MAXX(FILTER('CostData','CostData'[Start Date]<=MAX('Calendar'[Date]) && 'CostData'[End Date]>=MIN('Calendar'[Date]) ) , 'CostData'[Start Date])
RETURN MAXX(FILTER('CostData','CostData'[Start Date]=__StartDateOfCostToUse ) , 'CostData'[Cost]) // positive match
and
SelectedCost =
VAR __CostInBetween = LASTNONBLANKVALUE(
FILTER(
ALL('Calendar'[Date]),
'Calendar'[Date]<=MAX('Calendar'[Date])
),
[FirmCost]
)
VAR __EarliestStartDate = MIN( CostData[Start Date] )
VAR __EarliestCost = MAXX(FILTER('CostData','CostData'[Start Date]= __EarliestStartDate) , 'CostData'[Cost])
RETURN if(__CostInBetween = BLANK(), __EarliestCost, __CostInBetween )
Hi,
Alpha (whether One or Two), starts from Jan 14. In your result, why should there by any data from Jan 1 to Jan 13? Also, if you want to control the result with a slicer, then your should be writing a measure to solve this question (rather then runing an M code).
Hi, could you elaborate what the measure/slicer solution might look like? I'm open to approaching this in a different way - essentially i want to be able to view cost bulidup as a function of time (cost history, cost roadmap) so if a measure approach is best practice, this could be useful insight to me and others.
Please answer the question which i asked in my previous message. If my understand is correct as per the question asked, then show the revised result.
The reason for populating 1-13th January is because when we don't always have cost information for all date range, i don't really want zero cost if the user selects a date in the past, so closest known data is better than none, but I do plan to create a warning flag for these cases. I can understand your point though, there is not data here, so i should not create it. I think the case for 28-30th is more valid - the quote range ended, so the assumption is that the cost remains the same unless we get a new quote, although i would again create a flag or colour coding to indicate that cost is baesd on expired quote
Happy to hear a solution with a different assumption regarding early cost information - I could address this in another way (ask for historical costs to be loaded)
Thank you for your time, i look forwrad to hearing of another approach
Thank you - this is interesting and helpful example - i am still working through applying / extending to my problem, but i wanted to post an interim update
Using this measure, i can build the basic functionality as follows:
CostToUse = MAXX(FILTER('CostData','CostData'[Start Date]<MAX('Calendar'[Date]) && 'CostData'[End Date]>=MIN('Calendar'[Date])) , 'CostData'[Cost])
I'm not sure yet how this DAX can be changed to extend the cost value beyond the end date of the last available quote - ie through to the 30/1 date? (I got the correct functionality for "Alpha" by removing the second half of the FILTER clauses that checked for end date, but then realised that this only works in the case that costs always go up, due to the use of MAXX, when i put a cost reduction in the source data for Beta, it doesn't work)
Any suggestion welcome, i'll post back here if i make progress myself
Adding my solution here for completeness - maybe not the most elegant but it works
I will not mark this as a solution as it does not solve the original task of building a table of all dates in M/power query
CostToUse =
VAR __CostIfAvail = MAXX(FILTER('CostData','CostData'[Start Date]<=MAX('Calendar'[Date]) && 'CostData'[End Date]>=MIN('Calendar'[Date]) ) , 'CostData'[Cost])
VAR __LatestEnd = MAX( CostData[End Date] )
VAR __EarliestStart = MIN( CostData[Start Date] )
VAR __CostEarliest = MAXX(FILTER('CostData','CostData'[Start Date]= __EarliestStart) , 'CostData'[Cost])
VAR __CostLatest = MAXX(FILTER('CostData','CostData'[End Date]= __LatestEnd) , 'CostData'[Cost])
VAR __CostInferred = IF( MAX('Calendar'[Date]) < __EarliestStart, __CostEarliest, __CostLatest)
RETURN IF( __CostIfAvail = BLANK() , __CostInferred , __CostIfAvail )
...replying again to my own thread in case it is useful to somebody else
I extended my use case a bit to make it robust, and hence had to update my DAX
Other scenarios i wanted to cover - overlapping quotes, and gaps in quotes. The below addresses all of these now to my satisfaction.
FirmCost = // the cost strictly according to quotes - is blank if no quote is in range
VAR __StartDateOfCostToUse = MAXX(FILTER('CostData','CostData'[Start Date]<=MAX('Calendar'[Date]) && 'CostData'[End Date]>=MIN('Calendar'[Date]) ) , 'CostData'[Start Date])
RETURN MAXX(FILTER('CostData','CostData'[Start Date]=__StartDateOfCostToUse ) , 'CostData'[Cost]) // positive match
and
SelectedCost =
VAR __CostInBetween = LASTNONBLANKVALUE(
FILTER(
ALL('Calendar'[Date]),
'Calendar'[Date]<=MAX('Calendar'[Date])
),
[FirmCost]
)
VAR __EarliestStartDate = MIN( CostData[Start Date] )
VAR __EarliestCost = MAXX(FILTER('CostData','CostData'[Start Date]= __EarliestStartDate) , 'CostData'[Cost])
RETURN if(__CostInBetween = BLANK(), __EarliestCost, __CostInBetween )
Hi @jpc ,
Please refer to my pbix file to see if it helps you.
Create a date table includes the date from 2020/1/1 to 2020/1/30.
Then merge the 2 tables.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCizNL0lV0lHyLEnNBVLBJYlFJQouiWAx17wUGNM5v7hEKVYnWsk/D8R1zCnISATSRgZGBvqG+oYmCLaREZBtqGdoiKTcKbUEn2ojPSCFUO2emJuLT7mxnrExWHlIeT6mW4yMkdjmYLeYmyMpR3ULhmojPQsLpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Start Date", type date}, {"End Date", type date}, {"Cost", type number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Start Date"}, date, {"date"}, "Table (5)", JoinKind.RightOuter),
#"Expanded Table (5)" = Table.ExpandTableColumn(#"Merged Queries", "Table (5)", {"date"}, {"Table (5).date"}),
#"Filled Up" = Table.FillUp(#"Expanded Table (5)",{"Cost", "End Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Up",{"End Date", "Start Date"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Table (5).date"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Table (5).date", "date"}}),
#"Filled Down" = Table.FillDown(#"Renamed Columns",{"date", "Cost", "Item"}),
#"Filled Up1" = Table.FillUp(#"Filled Down",{"Item"}),
#"Reordered Columns" = Table.ReorderColumns(#"Filled Up1",{"Item", "Quote", "Cost", "date"})
in
#"Reordered Columns"
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
93 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |