Forum Discussion
Transform Table as a Custom Function
I have the following two steps that I will have to repeat multiple times. I would like to create a Custom function to do this but I can't seem to get it to work correctly.
#"Transform to Table" = Table.TransformColumns(#"Replaced Errors5", {{"MixedColumn", each if Value.Is(_, type table) then _ else #table({"Element:Text"}, {{_}})}}),
#"Expanded MixedColumn" = Table.ExpandTableColumn(#"Transform to Table", "MixedColumn", {"Element:Text"}, {"MixedColumn.Element:Text"}),
For instance this is returning a list instead of a table...
= (Input as any) =>
let
ToTable = ({{Input, each if Value.Is(_, type table) then _ else #table({"Element:Text"}, {{_}})}})
in
ToTable
5 Replies
What are you actually trying to achieve? Convert lists to tables?
Yep. I have a column that is returning a mix of text values and tables. Data is coming from a SharePoint list. I have to convert all data to tables first and then expand the results to read everything again.
Are these lookup fields in your sharepoint lists? Might be better to import all related tables from Sharepoint and do the lookups/links in the Power BI data model. Performance will be MUCH better that way.
They are not lookup fields. They are multi-line text fields.
Multi line text fields are not tables. Do you want to convert their content to tables?
