Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
Am taking data from API and while doing it some time the table is empty so while refreshing the data am getting error that the column is not found since am expanding the column in later steps so the column itself is not getting found so power query is throwing me error.
Can anyone help me with the code to overcome this callenge.
this is what i have tried and with this am able to handle the empty table scenario but the next steps(expansion of the column) is getting failed with the same error that colum is not found.
Code:
let
Source = Json.Document(Web.Contents("https://api.pilot.io/api/v1",[RelativePath="/recommendations?filters[type]=nat", Headers=[apikey="xxxxxxxxxxxx",
Authorization=Authorization()]])),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
custom= if Table.IsEmpty(#"Converted to Table") then Dummy_Table else #"Converted to Table",
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"region", "active_connections", "max_active_connections", "days_to_check", "uuid", "potential_savings", "type"})
in
#"Expanded Column1"
error:
"Expression.Error: The column 'Column1' of the table wasn't found.
Details:
Column1"
here am unable to handle the "Expanded Column1" steps and i want this steps to build my model so looking for any suggestion how to handle it.
Solved! Go to Solution.
pls try this
let
Source = Json.Document(Web.Contents("https://api.pilot.io/api/v1")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"getFile"}, null, ExtraValues.Error),
custom= if Table.IsEmpty(#"Converted to Table") then Dummy_Table else #"Converted to Table",
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "getFile", {"region", "active_connections", "max_active_connections", "days_to_check", "uuid", "potential_savings", "type"})
in
#"Expanded Column1"
Hi @Ahmedx thank you so much, it works.
i have created dummy table where in i have some value which i wanted the query to return when the source table is empty.
we still need to figure out what Dummy Table is returned here in case the table is empty
pls try this
let
Source = Json.Document(Web.Contents("https://api.pilot.io/api/v1")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"getFile"}, null, ExtraValues.Error),
custom= if Table.IsEmpty(#"Converted to Table") then Dummy_Table else #"Converted to Table",
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "getFile", {"region", "active_connections", "max_active_connections", "days_to_check", "uuid", "potential_savings", "type"})
in
#"Expanded Column1"
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |