Hello!
I have built an M-Query script (lets call it Script A) that combines Excel spreadsheets in an on-premises folder into one large table. However, if one of these excel spreadsheets is formatted differently (for example, different tab name), the refresh breaks.
This PBI file has another M-Query script (lets call it Script B) that combines excel spreadsheets from a different on-premises folder. I would like for the refresh to work (i.e. for Script B to run), even if Script A fails.
In order to accomplish this, I set up a try-otherwise block around the entire Script A. If Script A breaks, I coded it to return the string "failed."
Ultimately, what I want is for my report to refresh, even if one of the scripts fails. Its okay if data from one of the scripts isn't loaded into PBI.
However, when I set up the dataset to break when the M-query runs, and when Irefresh my report, I get the following error for that dataset:
OLE DB or ODBC error: [Expression.Error] We cannot convert the value "failed" to type Table.. An unexpected exception occurred.
Here is my code:
let
source =
try(
let
Source = Folder.Files("\\uasshare003\\foldershares\Disposition"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Campaign Name", type text}, {"Disposition Name", type text}, {"Media Type Name", type text}, {"Skill Name", type text}, {"Contact ID", Int64.Type}, {"Contact Agent Name", type text}, {"Contact Start Date Time", type datetime}, {"Contact End Date Time", type datetime}, {"Contact Duration", type text}, {"Prequeued", Int64.Type}, {"Prequeue Abandons", type any}, {"IVR Time", type text}, {"Queued", Int64.Type}, {"Inbound Handled", Int64.Type}, {"Abandons", Int64.Type}, {"Abandon Time", type time}, {"Outbound", type any}, {"Outbound Handled", type any}, {"Handle Time", type time}, {"Holds", Int64.Type}, {"Hold Time", type time}, {"Refused", Int64.Type}, {"Speed Of Answer", type text}, {"Inbound Handle Time", type time}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each true),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Contact Duration", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Contact Duration.1", "Contact Duration.2", "Contact Duration.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Contact Duration.1", Int64.Type}, {"Contact Duration.2", Int64.Type}, {"Contact Duration.3", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Contact Duration.1", "Contact Duration (hours)"}, {"Contact Duration.2", "Contact Duration (mins)"}, {"Contact Duration.3", "Contact Duration (secs)"}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Renamed Columns", "IVR Time", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"IVR Time.1", "IVR Time.2", "IVR Time.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"IVR Time.1", Int64.Type}, {"IVR Time.2", Int64.Type}, {"IVR Time.3", Int64.Type}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type2",{{"IVR Time.1", "IVR Time (hours)"}, {"IVR Time.2", "IVR Time (mins)"}, {"IVR Time.3", "IVR Time (secs)"}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Renamed Columns2", "Speed Of Answer", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Speed Of Answer.1", "Speed Of Answer.2", "Speed Of Answer.3"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Speed Of Answer.1", Int64.Type}, {"Speed Of Answer.2", Int64.Type}, {"Speed Of Answer.3", Int64.Type}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type3",{{"Speed Of Answer.1", "Speed Of Answer (hours)"}, {"Speed Of Answer.2", "Speed Of Answer (mins)"}, {"Speed Of Answer.3", "Speed Of Answer (secs)"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns3", each ([Campaign Name] <> "Admin") and ([Skill Name] <> "Test IB Skill" and [Skill Name] <> "Test OB Skill"))
in
#"Filtered Rows1"
)
otherwise
(
"failed"
)
in
source
