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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kgiboin
Helper I
Helper I

Table.ColumNames loading for too long

Hi.

I have a code that looks like this (redacted where necessary).

 

let
Source = SharePoint.Files("https://sharepoint.com/sites/Example", [ApiVersion = 15]),
#"Filtered Row" = Table.SelectRows(Source, each ([Folder Path] = "https://sharepoint.com/sites/Example/file")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Row", each [Attributes]?[Hidden]? <> true),
#"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File", each #"Transform File"([Content])),
#"Renamed Column1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Column1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Promoted Headers1" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
#"S11" = Table.ReplaceValue(#"Promoted Headers1","","0",Replacer.ReplaceValue,{"Package Name"}),
#"Filtered Rows" = Table.SelectRows(S11, each ([Part Number] <> null)),
#"S13" = Table.ColumnNames(#"Filtered Rows"),
#"S14" = Table.FromList(#"S13", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"S15" = Table.SelectRows(Table.Buffer(S14), each ([Column1] <> "Supplier 1" and [Column1] <> "Supplier  2" and [Column1] <> "Supplier 3" and [Column1] <> "supplier 4" .... to "Supplier nth"
#"Filtered Rows1" = Table.SelectRows(S15, each ([Column1] <> "Supplier 1" and [Column1] <> "Supplier 2" ....

#"S16" = #"Filtered Rows1"[Column1],
#"S17" = Table.SelectColumns(#"Filtered Rows",#"S16"),

 

 

This code is to create dynamic headers following steps I found online. It has worked for months but now I am finding that step 13 (S13) is loading indefinitely. 

 

 

1- how does the Table.ColumnNames function operate and why is it taking so long?

2- why is this happening now when nothing has changed?

3- is there a way to do this less memory expensively (assuming thats the issue)

 

Kind regards

 

3 REPLIES 3
kgiboin
Helper I
Helper I

Hi,

So 2 days later when I tried to run this again, it worked without any issues...

I am none the wiser as to why it failed. Could there have been a problem with the transformation files?

Regards

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @kgiboin - what you are doing it rather complex?  How many files are you importing at #"Invoke Custom Function1"?  This could be the cause of your performance issues. 

You could make S13 less expensive because the column names could using the following functions:

ColHead = List.Buffer( Table.ColumnNames(#"Transform File"(#"Sample File") ),
ExpHead = List.Combine( { "Name.Source", ColHead } ),


You may be able to move this step Table.Buffer(S14)

S12 = Table.Buffer( S11 ),
S13 etc

 

I am not sure I follow what steps 13 to S17 including #"Filtered Rows" is achieving.   I don't see how Columns filters Rows.  I can't picture this.

Hi @kgiboin and @Daryl-Lynch-Bzy ,

 

The bit with ColHead as above may not work as intended as the columns names are not what they are in the S13 as it is beeing pushed up in the Promoted Headers step and no longer similar to the one returned in Transform File.

 

I think the code needs to be optimized a bit furher to include what is sgeested by @Daryl-Lynch-Bzy and some other bits:

 

let
    Source = SharePoint.Files("https://sharepoint.com/sites/Example", [ApiVersion = 15]),
    #"Filtered Row" = Table.SelectRows(Source, each ([Folder Path] = "https://sharepoint.com/sites/Example/file")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Row", each [Attributes]?[Hidden]? <> true),
    #"Filtered Hidden Files2" = Table.SelectRows(#"Filtered Hidden Files1", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files2", "Transform File", each #"Transform File"([Content])),
    #"Renamed Column1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Column1", {"Source.Name", "Transform File"}),
    
    ColHead = List.Buffer(Table.ColumnNames(#"Transform File"(#"Sample File"))), //put it into a buffer to make sure that it is only called once
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", ColHead),
    
    // This bit should be happening in the #"Transform File" function, just add those steps to the query (and the related function should update automatically)
        #"Promoted Headers1" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
        #"S11" = Table.ReplaceValue(#"Promoted Headers1","","0",Replacer.ReplaceValue,{"Package Name"}),
        #"Filtered Rows" = Table.SelectRows(S11, each ([Part Number] <> null)),
    // up to this point need to be moved to the transformation function
    
     #"S13" = {"Source.Name"} & List.Select(ColHead, each not List.Contains({"Supplier 1", "Supplier  2", "Supplier 3", "supplier 4" /*.... to "Supplier nth" */}, _)),,
    
    /* all these lines are no longer needed as S13 does it all

    #"S14" = Table.FromList(#"S13", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"S15" = Table.SelectRows(Table.Buffer(S14), each ([Column1] <> "Supplier 1" and [Column1] <> "Supplier  2" and [Column1] <> "Supplier 3" and [Column1] <> "supplier 4" .... to "Supplier nth"
    #"Filtered Rows1" = Table.SelectRows(S15, each (_ <> "Supplier 1" and [Column1] <> "Supplier 2" ....
    #"S16" = #"Filtered Rows1"[Column1],
    
    */

    #"S17" = Table.SelectColumns(#"Filtered Rows", S13)
in #"S17"

 

 

Cheers,

John

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors
Users online (1,088)