cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Samph Frequent Visitor
Frequent Visitor

Expand Columns with missing fields

I have a query with a grouped column that contains a field "customDomains" however this field is sometimes empty. When I expand the column it results in the table only showing rows up until the first entry that does not have anything in "customDomains".

 

I.e

 

This is the table before i try to expand:


2019-04-03 13_42_48-PartnerCenterAPI - Power Query Editor.png
With the below error on the 3rd row:

2019-04-03 13_43_26-PartnerCenterAPI - Power Query Editor.png

After selecting to expand, it only expands the first 2 rows and stops at the first error. 

 

Is there a way to ignore the error and expand the rows after it?

This is the powerquery with the expand:

 

let
pcToken = GetPartnerCenterToken(<OMITTED>), 

customers = GetCustomers(pcToken), #"Renamed Columns" = Table.RenameColumns(customers,{{"companyName", "Company Name"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns", {"id"}, {{"GroupedRows", each _, type table}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "GetCustomerDetailsFunc", each GetCustomerDetailsFunc([id])),
    #"Expanded GroupedRows" = Table.ExpandTableColumn(#"Invoked Custom Function", "GroupedRows", {"domain", "Company Name"}, {"GroupedRows.domain", "GroupedRows.Company Name"}),
    #"Expanded GetCustomerDetailsFunc" = Table.ExpandTableColumn(#"Expanded GroupedRows", "GetCustomerDetailsFunc", {"customDomains"}, {"GetCustomerDetailsFunc.customDomains"})
in
    #"Expanded GetCustomerDetailsFunc"

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Expand Columns with missing fields

Hi @Samph ,

I'd like to suggest you to use #table function to define a empty table with same data structure and used to replace error values.

Creating Tables In Power BI/Power Query M Code Using #table()

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Expand Columns with missing fields

Hi @Samph ,

I'd like to suggest you to use #table function to define a empty table with same data structure and used to replace error values.

Creating Tables In Power BI/Power Query M Code Using #table()

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,295)