cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qsmith83
Responsive Resident
Responsive Resident

Help with Power Query Date Table returning Error for some columns

Hi,
I have this script for a date table but the "Offset" columns are returning error. The script was sourced from Radcad site  and the only change I made to the script was input my 'api key' and changed the "from year" & "to year". 
Appreciate any help @edhans @ronrsnfld 

 

 

let
    LocalToday=()=>
        let 
            Source = Xml.Document(Web.Contents("http://api.timezonedb.com/v2/get-time-zone?key=xyz=xml&by=zone&zone=NZST")),
            Value = Source{0}[Value],
            Value1 = Value{12}[Value]
        in
            Value1,
    // configurations start
    Today=Date.From(DateTime.From(LocalToday())), // today's date
    FromYear = 2022, // set the start year of the date dimension. dates start from 1st of January of this year
    ToYear=2023, // set the end year of the date dimension. dates end at 31st of December of this year
    StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
    firstDayofWeek=Day.Monday, // set the week's start day, values: Day.Monday, Day, Sunday....
    // configuration end
    FromDate=#date(FromYear,1,1),
    ToDate=#date(ToYear,12,31),
    Source=List.Dates(
        FromDate,
        Duration.Days(ToDate-FromDate)+1,
        #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"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date],firstDayofWeek), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
    FiscalMonthBaseIndex=13-StartofFiscalYear,
    adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
    #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),
    #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
    #"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
    #"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),
    #"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date]-Today, type duration),
    #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days",{{"Age", "Day Offset"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)
+([Month]-Date.Month(Today))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Month Offset", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Year Offset", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4)
+([Quarter]-Date.QuarterOfYear(Today))),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Quarter Offset", Int64.Type}})
in
    #"Changed Type4"

 

 



qsmith83_0-1657484142982.png

 



1 ACCEPTED SOLUTION
qsmith83
Responsive Resident
Responsive Resident

I believe I have fixed the issue by having a play with the day offset filters and seems to be working as it should. Thanks for your time and help.

View solution in original post

9 REPLIES 9
edhans
Super User
Super User

The URL doesn't work. It returns this:

edhans_0-1657492512610.png

 

I would not refer to any free website for a critical report. 

If you want a dynamic date table that can handle timezones and doesn't rely on websites, try Creating a Dynamic Date Table in Power Query and Add a Refresh Time Stamp To Your Power BI Reports — ehansalytics which will walk you through the process and show you how to build a table to help with DST issues. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
qsmith83
Responsive Resident
Responsive Resident

@edhans thanks for the resources but I already have a working refresh timestamp on my report.
I also have a current date table in power query.

My issue I have is with relative date filters on my report when publishing to the service i.e. "in the last 7days", "in the last 1day (including today)", "is in this day" - it changes the dates to UTC. My timezone is NZST/NZDT which is UTC-12hrs

I've tried many suggestions on the forum and Google but can't seem to find one that works i.e. the relative date filters on my report don't display correct date based on my local timezone.
I've been at it for days and it's frustrating. Would appreciate your help please on how to resolve this issue.

Pbix file if you need
https://drive.google.com/file/d/1UwIN4N2y8EX4SdMbtfm87sCcWlIIaZ4L/view?usp=sharing

qsmith83
Responsive Resident
Responsive Resident

@Vijay_A_Verma @wdx223_Daniel are you able to assist please?

Hi @qsmith83 ,

 

Relative date filtering in Power BI Service defaults to UTC time zone, and we cannot change the time zone at this time.

 

This script does not allow us to use the relative date filters correctly, rather it creates several Offset Date slicers to achieve the same result as the relative date slicers. That is, you need to use [Day Offset] for filtering.

 

vkkfmsft_0-1657683963018.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

@v-kkf-msft sorry for the delayed reponse but I think it may have fixed the issue as I forgot to include another condition for the OFFSET filters i.e. "and is less than or equal to 0". I will need to confirm tomorrow my timezone (NZST -12hrs) and its past midday here so I'll wait until tomorrow morning to confirm if offset filter is working correctly.

qsmith83
Responsive Resident
Responsive Resident

@v-kkf-msft my published report on PBi service is not showing correct date i.e. the Offset filters are not working as they should. They are still on UTC time. At the time of writing this message, my timezone is NZST July 19 09:00am but the date filter is showing data for July 18.
I private messaged you the pbix file for reference if you need.

Appreciate your help on how to resolve this please as I've tried and tested many suggestions but can't get it to work.

 

qsmith83_1-1658177224096.png

qsmith83_2-1658177533753.png

 

 

Hi @qsmith83 ,

 

Have you refreshed the dataset on the new day? I suspect it may be because the data has not been updated.

 

Best Regards,
Winniz

@v-kkf-msft , yes the dataset is refreshed automatically but the issue remains. 

qsmith83
Responsive Resident
Responsive Resident

I believe I have fixed the issue by having a play with the day offset filters and seems to be working as it should. Thanks for your time and help.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors