cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickTT
Helper II
Helper II

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 5
lbendlin
Super User III
Super User III

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

lbendlin
Super User III
Super User III

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.

lbendlin
Super User III
Super User III

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.

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

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors