Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jamalq123
Helper II
Helper II

Date Table source changes

When  I started working in power bi, I simply made a link from excel to make the date table. Please find below the M Language which I took from advanced Editor

 

let
Source = Excel.Workbook(File.Contents("D:\Jamal Qamar DATA\Data\Geo\FPC Rating and Program wise revenue\rating cy17.xlsx"), null, true),
dDate_Sheet = Source{[Item="dDate",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(dDate_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Month", Int64.Type}, {"Month name", type text}, {"Year ", Int64.Type}, {"Month Year", type text}})
in
#"Changed Type"

 

Now I want to use Calendar option i.e. "dDate1 = CALENDAR(DATE(2015,1,1),MAX('Consolidated Data'[Day]))" inspite of the old method.

 

Please guide me to resolve the issue, In the old method I have to update the excel file time to time, whereas in the c aledar option this is very easy to use and not to update the table.

 

Regards,

 

Jamal Qamar

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi Jamal,

 

I found a way to replace the code directly.  You can check it out in this file.

1. Create a function. (I found the M code here). 

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertMonth = Table.AddColumn(RenamedColumns, "Month", each Date.Month([Date])),
    InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertYear = Table.AddColumn(InsertMonthName, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "Month Year", each Date.QuarterOfYear([Date]))
       
  in
    InsertQuarter
in
  CreateDateTable

2. Replace the M code of the query dDate with the following one.

let
    maxDate = List.Max(#"Consolidated Data"[Column1]), //Max date of another query, which makes this query dynamic.
    Source = Query(#date(2018, 2, 1), maxDate, null)
in
    Source

3. Apply the changes.

 

Note: If the data you shared is a sample, you need to adjust the code to satisfy your data. The structure and the column names should be the same.

Now you can get rid of the workbook file.

 

Best Regards,

Dale

 

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

View solution in original post

11 REPLIES 11
v-jiascu-msft
Employee
Employee

Hi Jamal,

 

I found a way to replace the code directly.  You can check it out in this file.

1. Create a function. (I found the M code here). 

let CreateDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
  let
    DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertMonth = Table.AddColumn(RenamedColumns, "Month", each Date.Month([Date])),
    InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Date.ToText([Date], "MMMM", Culture), type text),
    InsertYear = Table.AddColumn(InsertMonthName, "Year", each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, "Month Year", each Date.QuarterOfYear([Date]))
       
  in
    InsertQuarter
in
  CreateDateTable

2. Replace the M code of the query dDate with the following one.

let
    maxDate = List.Max(#"Consolidated Data"[Column1]), //Max date of another query, which makes this query dynamic.
    Source = Query(#date(2018, 2, 1), maxDate, null)
in
    Source

3. Apply the changes.

 

Note: If the data you shared is a sample, you need to adjust the code to satisfy your data. The structure and the column names should be the same.

Now you can get rid of the workbook file.

 

Best Regards,

Dale

 

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

Waoooooo Dale,

 

Excellent task you did for me. Thanks a lot. Now my issue resolved. This is a relax time for me.

 

Best Regards,

 

Jamal Qamar

 

That's great! You are welcome, Jamal. Could you please mark my answer as solution?

 

Best Regards,

Dale

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

Yes Dale, I did it. Again thanks for your cooperation.

 

Regards,

 

Jamal Qamar

v-jiascu-msft
Employee
Employee

Hi Jamal,

 

What's the issue? You know the DAX function Calendar and you can create a date table.

Date_Table_source_changes

 

 

Best Regards,

Dale

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

Hi Dale,

 

Thanks for your response.

Actually I want to replace the Date field of dDate table with the one which you suggested. Then I will connect the date from my sales table.

As mentioned earlier, the date field is linked with Excel file and all the reports are based on this Date field. Every month I have to add month in excel table. 

 

I want to use the Calendar function inspite of linkage with Excel. 

 

Please download the pbix file from the link given below.

https://drive.google.com/open?id=1WR_ghdl-wEWS-iQZzY9zTUeOuo32gXq7

 

I shall be thankful to you that I you could use the Calendar function in Date field.

 

Regards,

 

Jamal Qamar

 

 

Hi Jamal,

 

I'm afraid you have to replace the whole dDate table with a new date table. I hope I didn't miss anything. What could be the problem?

 

Best Regards,

Dale

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

Hi Dale,

 

Is there any way to just replace Date field so that it could not hit the report. I tried to chnage in the advanced editor, but could not do so.

 

If you could just replace the date filed with the new date field, it will be highly helpful for me.

 

Regards,

 

Jamal Qamar

Hi Jamal,

 

Another concern is if you still want the dates to be dynamic. As we can see from the formula in your first post.

 

Best Regards,

Dale

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

Yes Sure,

The only purpose is to make it dynamic which should connect with my sales date table.

Surely if the new created date table has exactly the same structure, name and relationships the model will be “unaware” of the change?

The problem I see is how to have two tables with the same name. Which basically means you will have to create the new Table using power query and M code, and the substitue the M code in the current date table with the new code created in the new table. I think.




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.