BPI Desktop Advanced Editor Dynamic Value

[MarkCBB]

Hi there,

I am trying to get a dynamic value into the Advanced editor. below is the query, the last line you will see the value 4, I want that value to be dynamic i.e. 4 should be something like MONTH(TODAY())

let
Source = Folder.Files("D:\Dropbox\eXceler8\Projects\Excel Add-Ins\Clients\iRam Internal\Dev files\iRAM_ADDIN_APP_01\SSF\PBI_DATA_TABLES\MASTER_SITES"),
#"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "BACKUP ")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetExcelData", each Excel.Workbook([Content])),
#"Expanded GetExcelData" = Table.ExpandTableColumn(#"Added Custom", "GetExcelData", {"Name", "Data", "Item", "Kind", "Hidden"}, {"GetExcelData.Name", "GetExcelData.Data", "GetExcelData.Item", "GetExcelData.Kind", "GetExcelData.Hidden"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded GetExcelData", each ([GetExcelData.Kind] = "Sheet")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Content", "GetExcelData.Item", "GetExcelData.Kind", "GetExcelData.Hidden", "GetExcelData.Name"}),
#"Expanded GetExcelData.Data" = Table.ExpandTableColumn(#"Removed Columns", "GetExcelData.Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"GetExcelData.Data.Column1", "GetExcelData.Data.Column2", "GetExcelData.Data.Column3", "GetExcelData.Data.Column4", "GetExcelData.Data.Column5", "GetExcelData.Data.Column6"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded GetExcelData.Data"),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SITE_ID", type text}, {"SITE", type text}, {"SITE NAME", type text}, {"SITE RANKING", type text}, {"STATUS", type text}, {"LAST SEEN", type datetime}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","BTP ","BTD",Replacer.ReplaceText,{"SITE NAME"}),
#"Filtered Rows2" = Table.SelectRows(#"Replaced Value", each [SITE_ID] <> null and [SITE_ID] <> ""),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows2", {"SITE_ID"}),
#"Filtered Rows3" = Table.SelectRows(#"Removed Duplicates", each ([STATUS] = "Existing")),
#"Merged Queries" = Table.NestedJoin(#"Filtered Rows3",{"SITE"},LOCATION,{"SITE NUM"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"SITE NUM", "COUNTRY", "PROVINCE ", "LATITUDE", "LONGITUDE"}, {"NewColumn.SITE NUM", "NewColumn.COUNTRY", "NewColumn.PROVINCE ", "NewColumn.LATITUDE", "NewColumn.LONGITUDE"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded NewColumn",{"NewColumn.SITE NUM"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"NewColumn.COUNTRY", "COUNTRY"}, {"NewColumn.PROVINCE ", "PROVINCE"}, {"NewColumn.LATITUDE", "LATITUDE"}, {"NewColumn.LONGITUDE", "LONGITUDE"}}),
#"Filtered Rows4" = Table.SelectRows(#"Renamed Columns", each [LATITUDE] <> null and [LATITUDE] <> ""),
#"Inserted Merged Column" = Table.AddColumn(#"Filtered Rows4", "LOCATION", each Text.Combine({Text.From([LONGITUDE], "en-US"), Text.From([LATITUDE], "en-US")}, " "), type text),
#"Filtered Rows5" = Table.SelectRows(#"Inserted Merged Column", each [LATITUDE] <> null and [LATITUDE] <> ""),
#"Merged Queries1" = Table.NestedJoin(#"Filtered Rows5",{"PROVINCE"},REGIONS,{"RegionList"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"RegionList", "Region_Short", "LOGO", "UserName"}, {"NewColumn.RegionList", "NewColumn.Region_Short", "NewColumn.LOGO", "NewColumn.UserName"}),
#"Removed Columns2" = Table.RemoveColumns(#"Expanded NewColumn1",{"NewColumn.RegionList", "NewColumn.LOGO", "NewColumn.UserName"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns2",{{"NewColumn.Region_Short", "REGION"}}),
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns1",{"SITE RANKING", "LAST SEEN", "STATUS", "COUNTRY", "PROVINCE", "LOCATION", "REGION", "SITE", "LATITUDE", "LONGITUDE"}),
#"Merged Queries2" = Table.NestedJoin(#"Removed Columns3",{"SITE_ID"},MASTER_LINKS,{"SITE_ID"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries2", "NewColumn", {"ARTICLE_SITE_ID"}, {"NewColumn.ARTICLE_SITE_ID"}),
#"Merged Queries3" = Table.NestedJoin(#"Expanded NewColumn2",{"NewColumn.ARTICLE_SITE_ID"},MASTER_SALES,{"ARTICLE_SITE_ID"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn3" = Table.ExpandTableColumn(#"Merged Queries3", "NewColumn", {"YEAR", "MONTH_ID", "UNITS", "VALUE"}, {"NewColumn.YEAR", "NewColumn.MONTH_ID", "NewColumn.UNITS", "NewColumn.VALUE"}),
#"Removed Columns4" = Table.RemoveColumns(#"Expanded NewColumn3",{"SITE_ID", "NewColumn.ARTICLE_SITE_ID"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns4",{{"NewColumn.YEAR", "YEAR"}, {"NewColumn.MONTH_ID", "MONTH_ID"}, {"NewColumn.UNITS", "UNITS"}, {"NewColumn.VALUE", "VALUE"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns2", {"SITE NAME", "YEAR", "MONTH_ID"}, {{"UNITS", each List.Sum([UNITS]), type number}, {"VALUE", each List.Sum([VALUE]), type number}}),
#"Filtered Rows6" = Table.SelectRows(#"Grouped Rows", each [MONTH_ID] <= 4)
in
#"Filtered Rows6"

 

[ImkeF]

its: Date.Month(DateTime.LocalNow())

 

[MarkCBB]

Perfect, thank you