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
bartus1
Helper I
Helper I

Check for empty table before expandRecordColumn

Greetings,

 

My function gets a list of records per pEventID.  All works fine when a record is not empty, so it can expand the table - #"Expanded Column1"

 

Now, when this hits an empty record, I get an error - The column 'Column1' of the table wasn't found, which makes sense.

 

How do I prevent it and specify if record/table is empty, create EventsTable of null values???

 

Thanks.  

 

Funciton below:

---

 

let getEventItems = (pEventID as text) =>

let
Source = Json.Document(Web.Contents("...link/" & pEventID & "/GetItems")),
#"EventItemsTable" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
#"Expanded Column1" = Table.ExpandRecordColumn(#"EventItemsTable", "Column1", {"EventItemId", "EventItemTitle"}, {"EventItemId", "EventItemTitle"})

in
#"Expanded Column1"

in getEventItems

---

1 ACCEPTED SOLUTION

It was not clear that you had an empty table, I understood you had empty nested records in Column1,

 

In any case, you can use function Table.IsEmpty to check if the table is empty.

 

Another wild shot: if the table is empty and you don't have any columns, you can still create a table with Column1 with the record structure you would have if there would be a record, but without data. This will still let you expand Column1 and end up with the 2 columns, but without data.

 

Just to illustrate how it works: the code below has 2 Source steps, of which 1 must be commented (currently the first line).

The first will create a table with 1 row; the second will create an empty table.

 

let
//    Source = #table(1,{{[EventItemId = 1, EventItemDescription = "Description"]}}),
    Source = Table.FromList({}),
    CheckEmpty = if Table.IsEmpty(Source) then #table(type table[Column1 = [EventItemId = any, EventItemDescription = any]],{}) else Source,
    #"Expanded Column1" = Table.ExpandRecordColumn(CheckEmpty, "Column1", {"EventItemId", "EventItemDescription"}, {"EventItemId", "EventItemDescription"})
in
    #"Expanded Column1"

 

Specializing in Power Query Formula Language (M)

View solution in original post

4 REPLIES 4
MarcelBeug
Community Champion
Community Champion

You can remove rows with null in Column1 before expansion.

Alternatively you can replace nulls with an empty record before expansion.

 

I created some code to illustrate how that would look like:

 

let
    Source = #table(1,{{[EventItemId = 1, EventItemDescription = "Description"]},{null}}),
    NulRecord = Table.TransformColumns(Source,{{"Column1", each if _ = null then [] else _}}),
    #"Expanded Column1" = Table.ExpandRecordColumn(NulRecord, "Column1", {"EventItemId", "EventItemDescription"}, {"EventItemId", "EventItemDescription"})
in
    #"Expanded Column1"
Specializing in Power Query Formula Language (M)

Empty table for some ids.  Nothing to expand then.  Thanks.

Thank you kindly.

 

"You can remove rows with null in Column1 before expansion." - Well, I don't think I have Column1 at all when I get empty record.

 

Non empty record does return table which gets expanded.  My issue happens when empty record gets returned, so no Column1 exists to be expanded.

 

Sorry if I am lagging behind here...

It was not clear that you had an empty table, I understood you had empty nested records in Column1,

 

In any case, you can use function Table.IsEmpty to check if the table is empty.

 

Another wild shot: if the table is empty and you don't have any columns, you can still create a table with Column1 with the record structure you would have if there would be a record, but without data. This will still let you expand Column1 and end up with the 2 columns, but without data.

 

Just to illustrate how it works: the code below has 2 Source steps, of which 1 must be commented (currently the first line).

The first will create a table with 1 row; the second will create an empty table.

 

let
//    Source = #table(1,{{[EventItemId = 1, EventItemDescription = "Description"]}}),
    Source = Table.FromList({}),
    CheckEmpty = if Table.IsEmpty(Source) then #table(type table[Column1 = [EventItemId = any, EventItemDescription = any]],{}) else Source,
    #"Expanded Column1" = Table.ExpandRecordColumn(CheckEmpty, "Column1", {"EventItemId", "EventItemDescription"}, {"EventItemId", "EventItemDescription"})
in
    #"Expanded Column1"

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.