M Code Syntax to change Loaded Data

Power Query
1

Hello All,

I am relatively new to M Code and wonder if anyone can help me with syntax to dynamically change loaded date values.

The situation is i am looking at the Dashboarding and Data Visualisations course - Retail Sites moule with the "Retail Sites - Data Model.xlsx specifically the Sales Table “Purchase Date” but this could apply to any dataset with a Date value.

The dataset Purchase has values from 1/7/2016 - 31/12/2019 and we are now in April 2020.
As the dashboard uses dynamic date ranges calculated from TODAY - X days my idea was on load of the Dataset to Add 3 months or even 115 days to each value of the Purchase Date to have relevant sample date in the Dynamic reporting ranges of the Dashboard.

I just have not found the valid syntax to integrate Date.AddDays or AddMonths into the M CODE so if anyone can help on this it would be appreciated.

here is an example of the incorrect syntax

Sales_Data_Table = Source{[Item="Sales_Data",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sales_Data_Table,{{"Order ID", type text}, {"Product ID", type text}, {"Location ID", type text}, {"Sales Person ID", type text}, {"Customer ID", type text}, {"Purchase Date", type date}, {"Quantity", Int64.Type}}),
#"AddDaysToPurchaseDate" = Table.TransformColumnTypes(#"Changed Type",{"Purchase Date", Date.AddDays("Purchase Date",115 )})

in
#“AddDaysToPurchaseDate”

The Retail Sites example uses measures like

Revenue within Time Frame =
CALCULATE( [Total Revenue] ,
FILTER( Dates,
Dates[Date] <= TODAY() && Dates[Date] >= Today() - [Time Frame Selected] )
)

So the further the current date moves from the Max( [Purchase Date] ) the less data will found by the dynamic reporting ranges.

Another idea i had for working with this data set was not to change the loaded data set and Purchase dates but to trick the Current date in the Measure as follows
1. Create a Query Parameter to determine the Maximum value of the Purchase date
which is currently 31/12/2019 perhaps add some days to it .

MaxPurchaseDate  = Date.AddDays(List.Max(#"Changed Type"[Purchase Date]),10)
  1. Replace the TODAY() function in the Measure with a reference to a Query value holding this date derived from the dataset

Thanking you in advance
Russell

2

Hi @russellw999
In Data View, add a custom column [Temp Order Date] using Date.AddDays with your desired increment, then you can update the [Order Date] value. Hope this helps. Greg

let
        Source = Excel.Workbook(File.Contents("C:\Power BI\Sample Sales.xlsx"), null, true),
        Sales_Data_Table = Source{[Item="Sales_Data",Kind="Table"]}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Sales_Data_Table,{{"Order ID", type text}, {"Product ID", type text}, {"Customer ID", type text}, {"Order Date", type date}, {"Quantity", Int64.Type}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddDays([Order Date], 115)),
        #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Temp Order Date"}}),
        #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",each [Order Date], each [Temp Order Date], Replacer.ReplaceValue,{"Order Date"})
    in
        #"Replaced Value"
3

Hi Greg,

Thanks its a good simple solution, it looks like my idea of making the change on the same column is not supported in the M Code syntax.

I implemented your solution in the following manner. It would be equally valid to drop the original column and rename the new temporary column to the name of the original.

I also create a Parameter Query on the orignal date column at the Navigation step to save the original Maximum Value of the data set so that if i come back to use this report file in 6 months time then i can see how many months or days i need to add step he data set forward to the current date so that the measures return data

= List.Max(Sales_Data_Table[Purchase Date])

let
Source = Excel.Workbook(File.Contents(QueryDataFile), null, true),
Sales_Data_Table = Source{[Item=“Sales_Data”,Kind=“Table”]}[Data],
#“Changed Type” = Table.TransformColumnTypes(Sales_Data_Table,{{“Order ID”, type text}, {“Product ID”, type text}, {“Location ID”, type text}, {“Sales Person ID”, type text}, {“Customer ID”, type text}, {“Purchase Date”, type date}, {“Quantity”, Int64.Type}}),
#“Added Custom” = Table.AddColumn(#“Changed Type”, “Order Date”, each Date.AddDays([Purchase Date],115)),
#“Changed Type1” = Table.TransformColumnTypes(#“Added Custom”,{{“Order Date”, type date}}),
#“Reordered Columns” = Table.ReorderColumns(#“Changed Type1”,{“Order ID”, “Product ID”, “Location ID”, “Sales Person ID”, “Customer ID”, “Purchase Date”, “Order Date”, “Quantity”}),
#“Replaced Value” = Table.ReplaceValue(#“Reordered Columns”,each [Purchase Date], each [Order Date], Replacer.ReplaceValue,{“Purchase Date”}),
#“Removed Columns” = Table.RemoveColumns(#“Replaced Value”,{“Order Date”})

in
#“Removed Columns”

Thanks
Russell

4

Hi @russellw999,

Came across your post and thought I would share another approach. Who knows you or someone else might find it useful in the future… So try using Table.TransformColumns, created an if-statement with ‘some logic’ to serve as example below, you can change that to your specific needs of course.

let
    Source = Excel.Workbook(File.Contents(QueryDataFile), null, true),
    Sales_Data_Table = Source{[Item=“Sales_Data”,Kind=“Table”]}[Data],
    ChangedType = Table.TransformColumnTypes(Sales_Data_Table,{{“Order ID”, type text}, {“Product ID”, type text}, {“Location ID”, type text}, {“Sales Person ID”, type text}, {“Customer ID”, type text}, {“Purchase Date”, type date}, {“Quantity”, Int64.Type}}),
    ChangeDates =
        let fxDateOffset = (dDate) => if Date.Year(dDate) <2018 then Date.AddYears( dDate, 3 ) + Duration.From( 115 ) else dDate,
            ConvertedDates = Table.TransformColumns( ChangedType, {{"Purchase Date", fxDateOffset, type date }})
        in
            ConvertedDates
in
    ChangeDates

.
I hope this is helpful.

5

Hi Melissa,
Thank you it was very helpful, i have also discovered your videos on m Code and Extended Date Table / Holiday table they help a lot as well
Regards
Russell