Forum Discussion
How to add static Column headers when i get "no data found" record instead of column headers.
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