Forum Discussion

timalford's avatar
timalford
Regular Visitor
4 years ago
Solved

Date table only showing current week

I am building a web scraper that will ping a website using a date field.  I plan on having a query that invokes a custom function to get the data from the website using a parameter (date).  However, the website is only valid with the current week, so I cannot have every date in my date table.  When I refresh the query, I want it only to show the current 7 days of the week, starting with Monday and ending with the following Sunday.

 

This week would be:

 

8/22/2022Custom Invoked Function
8/23/2022Custom Invoked Function
8/24/2022Custom Invoked Function
8/25/2022Custom Invoked Function
8/26/2022Custom Invoked Function
8/27/2022Custom Invoked Function
8/28/2022Custom Invoked Function

 

I know typically you would filter out the current week in the visualization, but because of the website limitations, I dont want to invoke the function on dates outside of the current week.

 

All the info I've seen around various forums discuss doing it with DAX, but I need to do it with M Code in the Query Builder.

 

Thoughts?  Questions?  Any help is appreciated!!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi timalford ,

     

    I suggest you to try this code to generate a dynamic week table in Power Query.

    let
    Source = Date.From(DateTime.FixedLocalNow()),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Today"}}),
    #"Added WeekStart" = Table.AddColumn(#"Renamed Columns", "WeekStart", each Date.AddDays([Today],-Date.DayOfWeek([Today],Day.Monday))),
    #"Added WeekEnd" = Table.AddColumn(#"Added WeekStart", "WeekEnd", each Date.AddDays([Today],6-Date.DayOfWeek([Today],Day.Monday))),
    #"Added WeekList" = Table.AddColumn(#"Added WeekEnd", "WeekList", each {Number.From([WeekStart])..Number.From([WeekEnd])}),
    #"Expanded WeekList" = Table.ExpandListColumn(#"Added WeekList", "WeekList"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded WeekList",{{"WeekList", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Today", "WeekStart", "WeekEnd"})
in
    #"Removed Columns"

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    timalford , You can create two dates like

     

    Date.From(DateTime.FixedLocalNow())

     

    and

     

    Date.AddDays(Date.From(DateTime.FixedLocalNow()),-7)

     

    You can use these two limit the range

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi timalford ,

     

    I suggest you to try this code to generate a dynamic week table in Power Query.

    let
    Source = Date.From(DateTime.FixedLocalNow()),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Today"}}),
    #"Added WeekStart" = Table.AddColumn(#"Renamed Columns", "WeekStart", each Date.AddDays([Today],-Date.DayOfWeek([Today],Day.Monday))),
    #"Added WeekEnd" = Table.AddColumn(#"Added WeekStart", "WeekEnd", each Date.AddDays([Today],6-Date.DayOfWeek([Today],Day.Monday))),
    #"Added WeekList" = Table.AddColumn(#"Added WeekEnd", "WeekList", each {Number.From([WeekStart])..Number.From([WeekEnd])}),
    #"Expanded WeekList" = Table.ExpandListColumn(#"Added WeekList", "WeekList"),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded WeekList",{{"WeekList", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Today", "WeekStart", "WeekEnd"})
in
    #"Removed Columns"

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.