Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I've been looking at the Advanced Editor for a couple of hours and do not see why this error is occurring. Below is the code from the Advanced Editor. Any help with a fresh pair of eyes would be appreciated!
let
Source = SharePoint.Files("https://dcocf.sharepoint.com/sites/dataproject", [ApiVersion = 15]),
#"Extracted text after delimiter" = Table.TransformColumns(Source, {{"Folder Path", each Text.AfterDelimiter(_, "https://dcocf.sharepoint.com/sites/DataProject/", 0), type text}}),
#"Split column by delimiter" = Table.SplitColumn(#"Extracted text after delimiter", "Folder Path", Splitter.SplitTextByDelimiter("/"), {"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7"}),
#"Changed column type" = Table.TransformColumnTypes(#"Split column by delimiter", {{"Folder Path.1", type text}, {"Folder Path.2", type text}, {"Folder Path.3", type text}, {"Folder Path.4", type text}, {"Folder Path.5", type text}, {"Folder Path.6", type text}, {"Folder Path.7", type text}}),
#"Filtered rows" = Table.SelectRows(#"Changed column type", each [Folder Path.2] = "WIP" and [Folder Path.3] = "WIP Reports" and [Folder Path.4] = "WIP TEST" and [Name] <> "WIP_Month_End_November_2023_Aggregated_113023.xlsm" and [Name] <> "WIP_SNAPSHOT_REPORT 2024-01-13 07 00.xlsx"),
Source2 = SharePoint.Files("https://dcocf.sharepoint.com/sites/Reporting", [ApiVersion = 15]),
Custom = Table.TransformColumns(Source2, {{"Folder Path", each Text.AfterDelimiter(_, "https://dcocf.sharepoint.com/sites/Reporting/", 0), type text}}),
#"Custom 1" = Table.SplitColumn(Custom, "Folder Path", Splitter.SplitTextByDelimiter("/"), {"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4", "Folder Path.5", "Folder Path.6", "Folder Path.7"}),
#"Filtered rows 3" = Table.SelectRows(#"Custom 1", each [Folder Path.1] = "ReleasedNotForConsumption" and [Name] = GetWIP),
#"Filtered rows 2" = Table.SelectRows(#"Filtered rows 3", each ([Folder Path.2] = "")),
#"Filtered rows 1" = Table.SelectRows(#"Filtered rows 2", each ([Folder Path.2] = "")),
#"Appended query" = Table.Combine({#"Filtered rows 1", #"Filtered rows"}),
#"Filtered rows 4" = Table.SelectRows(#"Appended query", each ([Name] = "WIP_SNAPSHOT_REPORT 2024-03-01 07 14.xlsx")),
#"Invoke custom function" = Table.AddColumn(#"Filtered rows 4", "Transform file", each #"Transform file"([Content])),
#"Removed other columns" = Table.SelectColumns(#"Invoke custom function", {"Transform file"}),
#"Expanded Transform file" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", {"ORDER GROUP NUMBER", "PROJECT EXECUTIVE", "CLIENT NAME", "PROJECT NAME", "PROJECTED CONTRACT TOTAL", "BILLED", "BALANCE TO FINISH", "REPORT DATE", "Project ID"}, {"ORDER GROUP NUMBER", "PROJECT EXECUTIVE", "CLIENT NAME", "PROJECT NAME", "PROJECTED CONTRACT TOTAL", "BILLED", "BALANCE TO FINISH", "REPORT DATE", "Project ID"}),
#"Changed column type 1" = Table.TransformColumnTypes(#"Expanded Transform file", {{"REPORT DATE", type date}, {"BALANCE TO FINISH", type number}, {"BILLED", type number}, {"PROJECT NAME", type text}, {"CLIENT NAME", type text}, {"PROJECT EXECUTIVE", type text}, {"ORDER GROUP NUMBER", type text}, {"PROJECTED CONTRACT TOTAL", type number}, {"Project ID", type text}}),
#"Merged queries 1" = Table.NestedJoin(#"Changed column type 1", {"Project ID"}, PrimaryLabels, {"Project ID"}, "PrimaryLabels", JoinKind.LeftOuter),
#"Expanded PrimaryLabels" = Table.ExpandTableColumn(#"Merged queries 1", "PrimaryLabels", {"Client Name", "Project Name", "Salesperson1"}, {"Client Name.1", "Project Name.1", "Salesperson"}),
#"Replaced value 1" = Table.ReplaceValue(#"Expanded PrimaryLabels", each [CLIENT NAME], each if [Client Name.1]<> null then [Client Name.1] else [CLIENT NAME], Replacer.ReplaceValue, {"CLIENT NAME"}),
#"Changed column type 3" = Table.TransformColumnTypes(#"Replaced value 1", {{"Project ID", type text}}),
#"Replaced value 3" = Table.ReplaceValue(#"Changed column type 3", each [PROJECT NAME], each if [Project ID] = [ORDER GROUP NUMBER] then [Project Name.1] else [PROJECT NAME], Replacer.ReplaceValue, {"PROJECT NAME"}),
#"Replaced value 4" = Table.ReplaceValue(#"Replaced value 3", each [PROJECT EXECUTIVE], each if [Salesperson]<>null then [Salesperson] else [PROJECT EXECUTIVE], Replacer.ReplaceValue, {"PROJECT EXECUTIVE"}),
#"Merged queries 4" = Table.NestedJoin(#"Replaced value 4", {"Project ID"}, Portfolio, {"Project ID"}, "Portfolio", JoinKind.LeftOuter),
#"Expanded Portfolio" = Table.ExpandTableColumn(#"Merged queries 4", "Portfolio", {"Project Name"}, {"Project Name.2"}),
#"Replaced value 5" = Table.ReplaceValue(#"Expanded Portfolio", each [PROJECT NAME], each if [Project Name.2]<>null then [Project Name.2] else [PROJECT NAME], Replacer.ReplaceValue, {"PROJECT NAME"}),
#"Uppercased text" = Table.TransformColumns(#"Replaced value 5", {{"PROJECT NAME", each Text.Upper(Text.From(_)), type nullable text}}),
#"Removed columns" = Table.RemoveColumns(#"Uppercased text", {"Client Name.1", "Project Name.1", "Salesperson", "Project Name.2"}),
#"Merged queries 3" = Table.NestedJoin(#"Removed columns", {"Project ID"}, PrimaryCloseDates, {"Project ID"}, "PrimaryCloseDates", JoinKind.LeftOuter),
#"Expanded PrimaryCloseDates" = Table.ExpandTableColumn(#"Merged queries 3", "PrimaryCloseDates", {"Order Delivery Date"}, {"PRIMARY CLOSE DATE"}),
#"Transform columns" = Table.TransformColumnTypes(#"Expanded PrimaryCloseDates", {{"PROJECT EXECUTIVE", type text}, {"CLIENT NAME", type text}, {"PROJECT NAME", type text}}),
#"Transform columns 1" = Table.TransformColumnTypes(#"Transform columns", {{"PRIMARY CLOSE DATE", type text}}),
#"Changed column type 2" = Table.TransformColumnTypes(#"Transform columns 1", {{"PRIMARY CLOSE DATE", type date}}),
#"Sorted rows" = Table.Sort(#"Changed column type 2", {{"REPORT DATE", Order.Ascending}})
in
#"Sorted rows"
Solved! Go to Solution.
It doesn't look like that there is subtraction in this query directly, but you are invoking a custom function. I'd look for issues with subtracting numbers that are text type in that function.
Thank you! I'll take a look and will let you know.
It doesn't look like that there is subtraction in this query directly, but you are invoking a custom function. I'd look for issues with subtracting numbers that are text type in that function.
Thank you! I found the subtraction of the text in the custom function. Thanks again!