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

Expand ALL columns with SOME containing a mix of tables and values

Hi folks,

Im am having a hard time solving the following problem and I´m hoping for your help:

 

I am using the following Function to expand all Tables in all columns of a Webservice call:

 

let
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
ColumnContents = Table.Column(TableToExpand, ColumnName),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is table then Table.ColumnNames(_) else {}))),
NewColumnNames = List.Transform(ColumnsToExpand, each Text.Replace(Text.Replace(ColumnName, "Table",""),".....","") & "." & _),
CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
ExpandedTable = if CanExpandCurrentColumn then Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames) else TableToExpand,
NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else ExpandAll(ExpandedTable, NextColumnNumber)
in
OutputTable
in
Source

 

It works fine as long as each column has only tables or only values, but once they are mixed an error occurs. 

I tried various solutions including the following: 

 Solved: Re: Expand Column Containing Some Tables - Microsoft Power BI Community

 

However I´m failing at incorporating the solution into the function mentioned above, so that it works for all nested tables.

 

So basically what I would like to achieve is the following:

 

The function should do the following:

if the column contains only tables expand them all (as it does already)

else if  the column contains a mix convert  the tables into lists then extract values and replace errors by null.

 

I would really appreciate any help! 🙂

 

Regards,

 

Nico 

2 REPLIES 2
soptim_vond
Frequent Visitor

The more I think about it, shouldnt the function be able to handle mixed columns since it iterates over each row in the column and checks whether it is of type table...so values should just be ignored, right?

 

Please help me, I am lost! 😅

Icey
Community Support
Community Support

Hi @soptim_vond ,

 

You can use "try ... otherwise ..." to incorporate the two solutions, in other words, to handle errors.

 

Reference:

M Language Error Handling - PowerQuery M | Microsoft Docs;

Error handling (IFERROR) errors from Excel files in Power BI / Power Query — Powered Solutions.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors