Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Stop PBI automatically adding function to remove column in staging dataflow

I have created a staging dataflow connected to a sharepoint folder. As I am on a Pro license, I have cretaed an initial table which has load disabled:

 

let
  Source = SharePoint.Files("FOLDER NAME", [ApiVersion = 15]),
  #"Split column by delimiter" = Table.SplitColumn(Source, "Folder Path", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Folder Path.1", "Folder Path.2"}),
  #"Split column by delimiter 1" = Table.SplitColumn(#"Split column by delimiter", "Folder Path.1", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"Folder Path.3", "Folder Path.4"}),
  #"Filtered rows" = Table.SelectRows(#"Split column by delimiter 1", each [Folder Path.3] = "FOLDER NAME/reports"),
  #"Removed columns" = Table.RemoveColumns(#"Filtered rows", {"Folder Path.2"})
in
  #"Removed columns"

 

I then reference it in a separate table which has load enabled for use in other dataflows:

let
  Source = #"All submissions",
  #"Filtered rows" = Table.SelectRows(Source, each [Folder Path.4] = "Q1 2022-23")
in
  #"Filtered rows"

 

The problem is when I save the dataflow and refresh it, PBI adds on a line which means I can't access the content of the excel files in another dataflow.

 

let
  Source = #"All submissions",
  #"Filtered rows" = Table.SelectRows(Source, each [Folder Path.4] = "Q1 2022-23"),
  #"Remove columns" = Table.RemoveColumns(#"Filtered rows", Table.ColumnsOfType(#"Filtered rows", {type table, type record, type list, type nullable binary, type binary, type function}))
in
  #"Remove columns"

 

I have tried using a "Choose columns" query but it keeps automatically adding that function in, how do I stop it?

2 Replies

  • hello

     

    Did you find an answer to this issue ? I'm facing the same and I have no clue how to prevent powerBI from adding this removeColumns step 😞

     

    Thanks

     

    G

  • Anonymous's avatar
    Anonymous
    Not applicable

    Try right click the query and untick the "Enabling staging"