Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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! 😅
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
48 | |
19 | |
13 | |
11 |