Transform Table as a Custom Function

[NickTT]

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

[lbendlin]

Multi line text fields are not tables.  Do you want to convert their content to tables?

[lbendlin]

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.

[NickTT]

They are not lookup fields. They are multi-line text fields.

[lbendlin]

What are you actually trying to achieve? Convert lists to tables?

[NickTT]

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.