Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
crejo
New Member

Converting a list of lists to a table takes ages

So I have the following:

 

 

    DiffList = Table.TransformRows(
        JoinedTable,
        (row) => List.Transform(
            ColumnNames,
            (colname) => Record.Field(row, colname) = Record.Field(Record.Field(row, "newFile"){0}, colname)
        )
    )

 

 

This returns a list (because apperently Table.TransformRows returns a list?):

{{a, b, c}, {d, e, f}, {g, h, i}}

Actual list contains only logical TRUE/FALSE values, but I'll use this list to illustrate what I want to accomplish.

 

Now I want to transform this list to a table like this:

 

 

| col1 | col2 | col3 |
|======|======|======|
|  a   |  b   |  c   |
|  d   |  e   |  f   |
|  g   |  h   |  i   |

 

 

 

My current approach is to do it with this function:

 

 

ColumnNames = {"col1", "col2", "col3"},
OutputTable = Table.FromRows(DiffList, ColumnNames)

 

 

 

However, even if I have just 2 rows this takes AGES to complete. How can I speed this up?

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Use Power Query Diagnostics to pinpoint which of the transforms is slow.  My bet is on the "JoinedTable"  - likely you have one or more merges in your code. Those are notoriously slow.

 

This part

let
    Source = {{"a", "b", "c"}, {"d", "e", "f"}, {"g", "h", "i"}},
    ColumnNames = {"col1", "col2", "col3"},
    OutputTable = Table.FromRows(Source, ColumnNames)
in
    OutputTable

is not slow.

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

Use Power Query Diagnostics to pinpoint which of the transforms is slow.  My bet is on the "JoinedTable"  - likely you have one or more merges in your code. Those are notoriously slow.

 

This part

let
    Source = {{"a", "b", "c"}, {"d", "e", "f"}, {"g", "h", "i"}},
    ColumnNames = {"col1", "col2", "col3"},
    OutputTable = Table.FromRows(Source, ColumnNames)
in
    OutputTable

is not slow.

I changed the NestedJoin to an InnerJoin, but it was still slow. The culprit seemed to be something else than the join. Getting the ColumnNames was slow.

I changed 

Table.Column(Layout, "Name")

to

Table.ColumnNames(Table1)

 

Where Table1 is one of the tables that is joined and Layout was a table containing all the column names etc of the tables.

Please provide sample data that fully covers your issue.

The JoinedTable is a Table.NestedJoin. Sadly there doesn't seem to be a Query Diagnostics in excel like there is in PowerBI, so I can't use that.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors