Forum Discussion
How to add static Column headers when i get "no data found" record instead of column headers.
Using below code the if statemetn is throwing error (Token Idnentifier Expected), looking for any simple solutions or solution to get rid of the error
let
Source = Excel.Workbook(Web.Contents(Mflow/NewDOM/Balancing%20Report%20from%20test.xlsx"), null, true),
#"CWP Transction Logs Report_1_Sheet" = Source{[Item="CWP Transction Logs Report_1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Transction Logs Report_1_Sheet", [PromoteAllScalars=true]),
CheckNoData = Table.HasColumns(#"Promoted Headers",{"No Data Available"}),
= if CheckNoData = "True"
then
let
#"Promoted Headers" = #table(
type table {"LOG_ID", "TRADINGPARTNER_ID" }
)
in #"Promoted Headers"
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{ {"LOG_ID", Int64.Type}, {"TRADINGPARTNER_ID", type text} })
else
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{ {"LOG_ID", Int64.Type}, {"TRADINGPARTNER_ID", type text} })
in
#"Changed Type"
Hi LeeSun ,
There's a couple of standard ways to handle this sort of issue, both covered very well by Chris Webb:
To ensure all columns are always present:
To handle source errors in general:
https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/
IMPORTANT: Make sure to read all the comments on both blogs as there's some crucial changes/updates suggested by other readers.
Pete
2 Replies
Hi LeeSun ,
There's a couple of standard ways to handle this sort of issue, both covered very well by Chris Webb:
To ensure all columns are always present:
To handle source errors in general:
https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/
IMPORTANT: Make sure to read all the comments on both blogs as there's some crucial changes/updates suggested by other readers.
Pete
Thanks Pete,
Given links has enabled me to complete my task. Below solution worked for me
let
Source = Excel.Workbook(Web.Contents("Mflow/NewDOM/Balancing%20Report%20from%20Conduent.xlsx"), null, true),
#"CWP Transction Logs Report_1_Sheet" = Source{[Item="CWP Transction Logs Report_1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"CWP Transction Logs Report_1_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LOG_ID", Int64.Type}, {"TRADINGPARTNER_ID", type text} }),
Custom1 = Table.SelectRows(#"Changed Type", each [ERROR_DESC] <> "Internal Outbound Service Error"),AlternativeOutput = #table(
type table
[
#"LOG_ID"=Int64.Type,
#"TRADINGPARTNER_ID"=text
],
{}
),
TestForError= try #"Changed Type",//If Source returns an error then return the alternative table output
//else return the value of the #"Changed Type" step
Output = if TestForError[HasError] then AlternativeOutput else Custom1
in
Output
