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
