Table.ColumNames loading for too long

[kgiboin]

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

[kgiboin]

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]

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.

[jbwtp]

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