Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
avinash176
Frequent Visitor

power query

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.

1 ACCEPTED 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"

View solution in original post

4 REPLIES 4
avinash176
Frequent Visitor

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.

Ahmedx
Super User
Super User

we still need to figure out what Dummy Table is returned here in case the table is empty

avinash176
Frequent Visitor

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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors