Hi
I have few dataflows (on shared capacity) which has a scheduled refresh and refresh fails for below listed error messages
Error: Expression.Error: We cannot convert the value null to type Logical
Error: DataFormat.Error: We couldn't convert to Number
I have a lengthy dataflow with 60 lines of (applied steps) performing required transformation or cleansing on data, It gets really difficult to go through each step in PBI Service to find the cause of the error, and I have checked this it do not show any error over there, yet at the time of (on demand or scheduled refresh) the data flow fails with 2 error messages above (shown in RED)
Is there an elegant way to Exception handle known errors (so that my refresh do not fail) or what I can do to add additional M code so that it remains full proof and safe from above shown errors and goes through a successful refresh
See the M code below
let
Source = AzureStorage.DataLake("prog"),
#"Filtered hidden files" = Table.SelectRows(Source, each [Attributes][Hidden] <> true),
#"Split column by character transition" = Table.SplitColumn(
#"Filtered hidden files",
"Name",
Splitter.SplitTextByCharacterTransition({"0" .. "9"}, (c) => not List.Contains({"0" .. "9"}, c)),
{"Name.1", "Name.2"}
),
#"Changed column type 3" = Table.TransformColumnTypes(
#"Split column by character transition",
{{"Name.1", Int64.Type}}
),
#"Filtered rows 1" = Table.SelectRows(
#"Changed column type 3",
each ([Name.1] = List.Max(#"Changed column type 3"[Name.1]))
),
#"Invoke custom function" = Table.AddColumn(
#"Filtered rows 1",
"Transform file",
each #"Transform file"([Content])
),
#"Merged columns" = Table.CombineColumns(
Table.TransformColumnTypes(#"Invoke custom function", {{"Name.1", type text}}),
{"Name.2", "Name.1"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"Name"
),
#"Renamed columns" = Table.RenameColumns(#"Merged columns", {{"Name", "Source.Name"}}),
#"Removed other columns" = Table.SelectColumns(
#"Renamed columns",
{"Source.Name", "Transform file"}
),
Navigation = #"Removed other columns"{0}[Transform file],
#"Replaced value" = Table.ReplaceValue(
Navigation,
" A",
"",
Replacer.ReplaceText,
{"Finish", "Start"}
),
#"Replaced value 1" = Table.ReplaceValue(
#"Replaced value",
"*",
"",
Replacer.ReplaceText,
{"Start", "Finish"}
),
#"Filtered rows 3" = Table.SelectRows(#"Replaced value 1", each [Activity ID] <> ""),
#"Split column by delimiter 1" = Table.SplitColumn(
#"Filtered rows 3",
"EWR_G4_COINS",
Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true),
{"EWR_G4_COINS.1", "EWR_G4_COINS.2"}
),
#"Inserted conditional column" = Table.AddColumn(
#"Split column by delimiter 1",
"EWR_G4_COINS",
each if [EWR_G4_COINS.2] = null then [EWR_G4_COINS.1] else [EWR_G4_COINS.2]
),
#"Removed columns 3" = Table.RemoveColumns(
#"Inserted conditional column",
{"EWR_G4_COINS.1", "EWR_G4_COINS.2"}
),
#"Merged queries" = Table.NestedJoin(
#"Removed columns 3",
{"Project ID"},
mappingProject,
{"crf7a_projectid"},
"mappingProject",
JoinKind.LeftOuter
),
#"Expanded mappingProject" = Table.ExpandTableColumn(
#"Merged queries",
"mappingProject",
{"Dates.Periods.Period"},
{"mappingProject.Dates.Periods.Period"}
),
#"Merged queries 1" = Table.NestedJoin(
#"Expanded mappingProject",
{"Activity ID"},
stagingCostCodeDictionary,
{"ACBS"},
"stagingCostCodeDictionary",
JoinKind.LeftOuter
),
#"Expanded stagingCostCodeDictionary" = Table.ExpandTableColumn(
#"Merged queries 1",
"stagingCostCodeDictionary",
{"Section"},
{"Section"}
),
#"Renamed columns 1" = Table.RenameColumns(
#"Expanded stagingCostCodeDictionary",
{{"mappingProject.Dates.Periods.Period", "DimProject.DimPeriod.Period Number"}}
),
#"Filtered rows" = Table.SelectRows(#"Renamed columns 1", each [Section] = null),
#"Removed columns" = Table.RemoveColumns(#"Filtered rows", {"Section"}),
#"Merged queries 2" = Table.NestedJoin(
#"Removed columns",
{"DimProject.DimPeriod.Period Number"},
mappingDates,
{"Periods.Next Period"},
"mappingDates",
JoinKind.LeftOuter
),
#"Expanded mappingDates" = Table.ExpandTableColumn(
#"Merged queries 2",
"mappingDates",
{"Period Start", "Period End"},
{"mappingDates.Period Start", "mappingDates.Period End"}
),
#"Renamed columns 2" = Table.RenameColumns(
#"Expanded mappingDates",
{
{"mappingDates.Period Start", "Dates Group By.Period Start"},
{"mappingDates.Period End", "Dates Group By.Period End"},
{"BL1 Start", "Base Line Start"},
{"BL1 Finish", "Base Line Finish"}
}
),
#"Merged queries 3" = Table.NestedJoin(
#"Renamed columns 2",
{"DimProject.DimPeriod.Period Number"},
mappingDates,
{"Periods.Period"},
"mappingDates",
JoinKind.LeftOuter
),
#"Expanded mappingDates 1" = Table.ExpandTableColumn(
#"Merged queries 3",
"mappingDates",
{"Periods.Pervious Period"},
{"mappingDates.Periods.Pervious Period"}
),
#"Renamed columns 3" = Table.RenameColumns(
#"Expanded mappingDates 1",
{{"mappingDates.Periods.Pervious Period", "mappingPeriod.Period Number"}}
),
#"Inserted conditional column 1" = Table.AddColumn(
#"Renamed columns 3",
"Finish Transformed",
each if [Finish] = null then [Start] else [Finish]
),
#"Changed column type" = Table.TransformColumnTypes(
#"Inserted conditional column 1",
{{"Finish Transformed", type date}}
),
#"Merged queries 4" = Table.NestedJoin(
#"Changed column type",
{"BL Project Finish"},
Dates,
{"Date"},
"Dates",
JoinKind.LeftOuter
),
#"Expanded Dates" = Table.ExpandTableColumn(
#"Merged queries 4",
"Dates",
{"Periods.Period"},
{"Periods.Period"}
),
#"Renamed columns 4" = Table.RenameColumns(
#"Expanded Dates",
{{"Periods.Period", "BL Project Finish Period"}}
),
#"Merged queries 6" = Table.NestedJoin(
#"Renamed columns 4",
{"Finish Transformed"},
Dates,
{"Date"},
"Dates",
JoinKind.LeftOuter
),
#"Expanded Dates 2" = Table.ExpandTableColumn(
#"Merged queries 6",
"Dates",
{"Periods.Period"},
{"Periods.Period"}
),
#"Renamed columns 6" = Table.RenameColumns(
#"Expanded Dates 2",
{{"Periods.Period", "Planned Finish Period"}}
),
#"Merged queries 7" = Table.NestedJoin(
#"Renamed columns 6",
{"EWR_Key interface365"},
crf7a_p6interface,
{"crf7a_interface365"},
"crf7a_p6interface",
JoinKind.LeftOuter
),
#"Expanded crf7a_p6interface" = Table.ExpandTableColumn(
#"Merged queries 7",
"crf7a_p6interface",
{"crf7a_group"},
{"crf7a_p6interface.crf7a_group"}
),
#"Renamed columns 7" = Table.RenameColumns(
#"Expanded crf7a_p6interface",
{{"crf7a_p6interface.crf7a_group", "Deliverable"}}
),
#"Merged queries 8" = Table.NestedJoin(
#"Renamed columns 7",
{"Activity ID"},
mappingBaselineDates,
{"Activity ID"},
"mappingBaselineDates",
JoinKind.LeftOuter
),
#"Expanded mappingBaselineDates" = Table.ExpandTableColumn(
#"Merged queries 8",
"mappingBaselineDates",
{"BL Project Finish", "Dates.Periods.Period"},
{"mappingBaselineDates.BL Project Finish", "mappingBaselineDates.Dates.Periods.Period"}
),
#"Merged queries 5" = Table.NestedJoin(
#"Expanded mappingBaselineDates",
{"Actual Finish"},
Dates,
{"Date"},
"Dates",
JoinKind.LeftOuter
),
#"Expanded Dates 1" = Table.ExpandTableColumn(
#"Merged queries 5",
"Dates",
{"Periods.Period"},
{"Dates.Periods.Period"}
),
#"Renamed columns 5" = Table.RenameColumns(
#"Expanded Dates 1",
{
{"mappingBaselineDates.Dates.Periods.Period", "Baseline Finish Period"},
{"mappingBaselineDates.BL Project Finish", "Baseline Finish"},
{"Dates.Periods.Period", "Actual Finish Period"}
}
),
#"Added custom 1" = Table.AddColumn(
#"Renamed columns 5",
"Budgeted Total Cost 2",
each
if [DimProject.DimPeriod.Period Number]
< 2020.02
and ([EWR_Funding] = "S04E1" or [EWR_Funding] = "S04E2" or [EWR_Funding] = "S04E3")
then
0
else
[Budgeted Total Cost]
),
#"Added custom 2" = Table.AddColumn(
#"Added custom 1",
"Earned Value Cost 2",
each
if [DimProject.DimPeriod.Period Number]
< 2020.02
and ([EWR_Funding] = "S04E1" or [EWR_Funding] = "S04E2" or [EWR_Funding] = "S04E3")
then
0
else
[Earned Value Cost]
),
#"Added custom 3" = Table.AddColumn(
#"Added custom 2",
"Planned Value Cost 2",
each
if [DimProject.DimPeriod.Period Number]
< 2020.02
and ([EWR_Funding] = "S04E1" or [EWR_Funding] = "S04E2" or [EWR_Funding] = "S04E3")
then
0
else
[Planned Value Cost]
),
#"Changed column type 2" = Table.TransformColumnTypes(
#"Added custom 3",
{
{"Budgeted Total Cost 2", Currency.Type},
{"Earned Value Cost 2", Currency.Type},
{"Planned Value Cost 2", Currency.Type}
}
),
#"Removed columns 2" = Table.RemoveColumns(
#"Changed column type 2",
{"Budgeted Total Cost", "Earned Value Cost", "Planned Value Cost"}
),
#"Renamed columns 8" = Table.RenameColumns(
#"Removed columns 2",
{
{"Budgeted Total Cost 2", "Budgeted Total Cost"},
{"Earned Value Cost 2", "Earned Value Cost"},
{"Planned Value Cost 2", "Planned Value Cost"}
}
),
#"Added custom 4" = Table.AddColumn(#"Renamed columns 8", "Planned Finish", each [Finish]),
#"Added custom 5" = Table.AddColumn(#"Added custom 4", "Planned Start", each [Start]),
#"Changed column type 1" = Table.TransformColumnTypes(
#"Added custom 5",
{
{"EWR_G4_COINS", type text},
{"Planned Finish", type date},
{"Planned Start", type date},
{"Finish Transformed", type date},
{"Finish", type date},
{"Start", type date}
}
)
in
#"Changed column type 1"
