The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I am trying to crete a simple spread sheet using and ODBC connection and Power querry. I data dump the ODBC connection into excel and then use that table in Power querry.
There is a Day of Week going back at least (2) years. I really only care about 'This Current' Week. I filter on 'Day.IsInCurrentWeek' and it starts on a Sunday. I would like it to start Monday to Sunday.
I have read multiple forms and watched YouTube but my M Coding is not where it needs to be. Any help would be apprecietated. Thank you
Solved! Go to Solution.
Use this for your last step
= Table.SelectRows(#"Reordered Columns3", each Date.IsInCurrentWeek(Date.AddDays([Day of Week],-1)))
Hi guys
I am trying to do something similar - I am based in Australia/Melbourne, I am using PowerBI Service to refresh my reports.
I am getting false return for function date.isincurrentweek when the report refreshes on Monday at around 4am Australia/Melbourne time as it is taking the UTC time (which is still in the prior week).
Is there anyway around this? Please see below my query
Thanks
let
Source = Query1(#date(2016, 1, 1), 5000, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "yearWeek", each Date.Year([Date])*100+Date.WeekOfYear([Date])),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"yearWeek"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 0, 1, Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Added Index",{{"Index", "WeekCounter"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "IsCurrentWeek", each Date.IsInCurrentWeek([Date]))
in
#"Added Custom1"
If you want to use Monday as the start day for Date.IsInCurrentWeek, use
Date.IsInCurrentWeek(Date.AddDays([Date],-1))
Hence, you will have to insert following step where #"Changed Type" is previous step
= Table.SelectRows(#"Changed Type", each Date.IsInCurrentWeek(Date.AddDays([Date],-1)))
Thank you,
I tried to enter the code you porovided into the advanced editor and it is giving me error. I have attached the current advianced editor code.
I can't see Date.IsInCurrentWeek anywhere in your code? If you could highlight, that would be great.
Thank you. At the very bottom. Let me know if this is not what you are looking for
Go to UI - Click on Filtered Rows1 in right pane - In Formula bar, delete everything and copy my code..
THANK YOU! I am sorry if this was very frustrating. I really apprecieate your help. This worked.
Thank you thank you thank you!
Use this for your last step
= Table.SelectRows(#"Reordered Columns3", each Date.IsInCurrentWeek(Date.AddDays([Day of Week],-1)))
I am so sorry. I am not getting this. Does that code go in place of the #"Filtered Rows1" or after? Does the very last line "in #Filtered Rows1" need to be changed after that. I put the code in and I just get this response.
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 |
---|---|
36 | |
23 | |
23 | |
21 | |
16 |