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.
Good Morning,
I was hoping for some help with Power Query on creating a custom date table. I have searched for this solution for many weeks and have yet to be able to find it. I am currently utilizing an excel file that lists the max workdays for each month in each row. I would like to move away from having to connect to an excel work book each time i want to have a date table but also want the ability to utlize in multiple reports without having to recreate calculated columns. Any help would be appreciated. Screenshots below for the expected outcome.
Solved! Go to Solution.
I have actually figured this out (At least for now). I added two group by steps. One to group by Max value of the Month to date work days and one for the yearly work days. I utilized the all rows feature and kept in the source table. See below for the result.
@cking2019 - Well, if it is anything like the DAX way of doing it, https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109 you would need a way to generate a calendar table and determine which days are not weekends.
Maybe @ImkeF and/or @edhans can help, I couldn't find anything obvious in the Power Query function reference:
https://docs.microsoft.com/en-us/powerquery-m/date-functions
@Greg_Deckler Thank you! I have my calendar table built with cumulative count of work days in the month. I just want the static number to run a workdays remaining analysis and some other measures based on the amount of workdays. I know i can add a calculated column but that would require me to re make the column each time i use the date table in a report. I have seen a solution from @ImkeF however, each time I attempt to utlize it in the table it tells me there is circular refrence and i believe this is because my holidays are joined in my date table. I am trying to get a standard date table for our company while not having to connect to another data source (Excel File).
It is a bit complicated in Power Query.
I have actually figured this out (At least for now). I added two group by steps. One to group by Max value of the Month to date work days and one for the yearly work days. I utilized the all rows feature and kept in the source table. See below for the result.
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 |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |