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, I have two tables, a period table and a sales table. I need to use the invoice creation timestamp on the sales table and assign the week number from the period table where the timestamp is between the timestamps. Is there any way to join/merge the tables so I get my desired result below?
Thanks!
Period Table
Sales Table (Extract)
Desired Result (Sales Table with WeekPeriod)
Solved! Go to Solution.
Hi there,
there is actually a pattern for this task that will probably increase speed: https://www.skillwave.training/vlookup-true-in-power-query/
Also see attached file.
It delivers correct results if the intervals are without gaps like in the data @pedanticpad gave in his initial thread.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
You could do it in query but it is easier done in DAX. Load both tables as is (with DateTime format for those columns), and use this expression in a calculated column on your Sales table. This assumes there is no relationship between the Sales and Period tables. If so, a small modification would be needed.
WeekPeriod = var currenttime = Sales[Timestamp]
return Calculate(min(Period[WeekNumber]), Period[WeekPeriod_StartTimeStamp]<=currenttime, Period[WeekPeriod_EndTimeStamp]>= currenttime)
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat, thanks for the help, but I would prefer to do it in power query rather than dax.
The following in M will do what you want.
#"Added Custom" =
Table.AddColumn(#"Changed Type", "Some Number", each let
varDate = [Sales Date]
in
Table.SelectRows(Table, each [Date1] <= varDate and [Date2] >= varDate)[Some Number]{0}
)
You can see it in this PBIX file.
Note my data has some errors in the results because my Sales table has dates not in the Table date ranges. If you have the same issue you could wrap in a try/otherwise construct.
If you want more help, please provide data per links below, not screenshots. Thanks!
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans that worked, but my refresh time for that table has gone from 30 seconds to nearly half an hour. Is there another option?
Unfortuntely there is no merge between ranges in Power Query, so my code is doing a lot of comparisons row by row and a Table.SelectRows() for every single item.
Another possiblility is to do a cartesian join. See if this helps. Not optimistic.
#"Added Custom1" =
Table.AddColumn(
#"Added Custom",
"Some Number Again",
each let
varDate = [Sales Date]
in
Table.SelectRows([Custom], each varDate >= [Date1] and varDate <= [Date2])[Some Number]{0}
)
Go grab my file again using the link above. It has been updated with a Sales2 table to show you the entire process.
Someone may be able to write more efficient code. Not sure if @ImkeF could improve this or not - but if anyone can, it is her.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi there,
there is actually a pattern for this task that will probably increase speed: https://www.skillwave.training/vlookup-true-in-power-query/
Also see attached file.
It delivers correct results if the intervals are without gaps like in the data @pedanticpad gave in his initial thread.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |