I'll paste here the error I get from the refresh history log and then the queries codes. The first query is the only one that loads to the data flow whilst the other ones have their option "Enable load" unmarked.
let
#"Shared Documents" = Source{[Name = "Documents"]}[Content],
#"POWER BI SOURCES" = #"Shared Documents"{[Name = "POWER BI SOURCES"]}[Content],
MAPPINGS = #"POWER BI SOURCES"{[Name = "MAPPINGS"]}[Content],
Navigation = MAPPINGS{[Name = "SCREENDRAGON_DIMENSIONS"]}[Content],
#"Filtered rows" = Table.SelectRows(Navigation, each [Name] = "Project by Stages"),
#"Expanded Content" = Table.ExpandTableColumn(#"Filtered rows", "Content", {"Content"}, {"Content.1"}),
#"Filtered hidden files" = Table.SelectRows(#"Expanded Content", each [Attributes]?[Hidden]? <> true),
#"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content.1])),
#"Removed other columns" = Table.SelectColumns(#"Invoke custom function", {"Transform file"}),
#"Expanded table column <<<" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file"))),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded table column <<<", {{"Stage Close Date", type date}, {"Stage Start Date", type date}}),
#"- - Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Stage Status"}, Stage_Order, {"StageStatusPK"}, "Stage_Order", JoinKind.LeftOuter),
#"- - Expanded Stage_Order" = Table.ExpandTableColumn(#"- - Merged Queries", "Stage_Order", {"Order"}, {"stage.Order"}),
#"*^* Sort Stage Close Date" = Table.Sort(#"- - Expanded Stage_Order", {{"Stage Close Date", Order.Descending}}),
#"*^* Sort Stage Start Date" = Table.Sort(#"*^* Sort Stage Close Date", {{"Stage Start Date", Order.Descending}}),
#"*^* Removed duplicates" = Table.Distinct(#"*^* Sort Stage Start Date", {"Stage ID"}),
#"Added StageDatesCheck true/false" = Table.AddColumn(#"*^* Removed duplicates", "StageDatesCheck", each if [Stage Start Date] <= [Stage Close Date] then true else false),
#"Replaced errors" = Table.ReplaceErrorValues(#"Added StageDatesCheck true/false", {{"Parent Project ID", null}, {"Stage Close Date", null}, {"Stage ID", null}, {"Stage Name", null}, {"Stage Start Date", null}, {"Stage Status", null}, {"Investment Stage", null}, {"Investment Type", null}, {"Template Stage Name", null}, {"StageDatesCheck", null}}),
#"Added Stage Duration" = Table.AddColumn(#"Replaced errors", "Stage Duration", each if [StageDatesCheck] = true then Duration.Days([Stage Close Date] - [Stage Start Date] ) else 0, type number),
#"Duplicated column - Copy (2)" = Table.DuplicateColumn(#"Added Stage Duration", "Template Stage Name", "Template Stage Name - Copy (2)"),
#"Extracted text before delimiter 1of2" = Table.TransformColumns(#"Duplicated column - Copy (2)", {{"Template Stage Name - Copy (2)", each Text.BeforeDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
#"Extracted text between delimiters 2of2" = Table.TransformColumns(#"Extracted text before delimiter 1of2", {{"Template Stage Name - Copy (2)", each Text.BetweenDelimiters(Text.From(_), "_", "_", 1, 3), type text}}),
#"Inserted text before delimiter" = Table.AddColumn(#"Extracted text between delimiters 2of2", "Text before delimiter", each Text.BeforeDelimiter([#"Template Stage Name - Copy (2)"], "_", 0), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted text before delimiter", {{"Text before delimiter", "CJN PprojID"}, {"Template Stage Name - Copy (2)", "PprojID_Stage00"}}),
#"** Filtered null + blanks" = Table.SelectRows(#"Renamed Columns", each [Template Stage Name] <> "" and [CJN PprojID] <> ""),
#"** quick check Match?" = Table.AddColumn(#"** Filtered null + blanks", "IsMatched", each [Parent Project ID] = [CJN PprojID]),
#"Removed Columns" = Table.RemoveColumns(#"** quick check Match?", {"IsMatched", "Parent Project ID", "StageDatesCheck"}),
#"Changed column type 1" = Table.TransformColumnTypes(#"Removed Columns", {{"Stage ID", Int64.Type}, {"Stage Name", type text}, {"Stage Status", type text}, {"Investment Stage", type text}, {"Investment Type", type text}, {"Template Stage Name", type text}, {"CJN PprojID", Int64.Type}, {"stage.Order", Int64.Type}}),
#"Inserted merged ProjStage Unique" = Table.AddColumn(
#"Changed column type 1",
"ProjStage Unique",
each
let
parts = {
Text.From([PprojID_Stage00]),
Text.From([Stage ID]),
if [Stage Start Date] <> null then Date.ToText([Stage Start Date], "yyyy-MM-dd") else null,
Text.From([stage.Order])
}
in
Text.Combine(List.RemoveNulls(parts), " / "),
type text
),
#"Inserted merged PprojID+StageID" = Table.AddColumn(#"Inserted merged ProjStage Unique", "ProjID+StageID", each Text.Combine({Text.From([CJN PprojID]), Text.From([Stage ID])}, "_"), type text),
#"Merged queries" = Table.NestedJoin(#"Inserted merged PprojID+StageID", {"CJN PprojID"}, Project_SD1, {"Project ID"}, "Project_SD1", JoinKind.LeftOuter),
#"Expanded Project_SD1" = Table.ExpandTableColumn(#"Merged queries", "Project_SD1", {"Region (for projections)"}, {"Region (for projections)"})
in
#"Expanded Project_SD1"
Query Stage_Order:
let
Navigation = Source{[Name = "Documents"]}[Content],
#"Navigation 1" = Navigation{[Name = "POWER BI SOURCES"]}[Content],
#"Navigation 2" = #"Navigation 1"{[Name = "MAPPINGS"]}[Content],
#"Navigation 3" = #"Navigation 2"{[Name = "Reporting_Dimensions.xlsx"]}[Content],
#"Imported Excel workbook" = Excel.Workbook(#"Navigation 3", null, true),
#"Navigation 4" = #"Imported Excel workbook"{[Item = "Stage_Order", Kind = "Table"]}[Data],
#"Changed column type" = Table.TransformColumnTypes(#"Navigation 4", {{"StageStatusPK", type text}, {"Order", Int64.Type}, {"IncomeAnaysisStage", type text}})
in
#"Changed column type"
Query Project_SD1:
let
#"Navigation 1" = Source{[Item = "Project_SD", Kind = "Table"]}[Data],
#"Changed column type" = Table.TransformColumnTypes(#"Navigation 1", {{"Project ID", Int64.Type}, {"Project Name", type text}, {"Project Number", type text}, {"Project Status", type text}, {"Project Type", type text}, {"Brand Name", type text}, {"Region (for projections)", type text}, {"Client", type text}, {"Subsidiary Client", type text}, {"Project_Currency", type text}, {"ClientCode k ProjectNo", type text}, {"Project Client Group", type text}, {"ProCliGroup.Order", Int64.Type}, {"Subsidiary_Code_ClientName", type text}, {"Project_Location", type text}, {"Client - Brand", type text}, {"sub.Location", type text}, {"sub.symbol", type text}})
in
#"Changed column type"
Thanks in advance!
Beatriz Albuquerque
