Power BI Dataflows and a fluid amount of columns

[srduval]

I have a report I am trying to put together that is taking a source of data from SQL and transposing it. The problem I am having is that the dataflow is expecting a certain number of columns to be present, but this report will vary it might be 300 columns one month and 200 the next and 500 the month after that.

How can I write a mscript to be flexiable in this manner?

Here is my script, all the explicitly named columns are guaranteed to 

let

  Source = Sql.Database("redacted", "redacted", [Query = "redacted"]),

  #"Sorted rows" = Table.Sort(Source, {{"ValueColumn", Order.Ascending}}),

  #"Filtered Rows" = Table.SelectRows(#"Sorted rows", each [Record_Type] <> "Org Owned"),

  #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"EMPLOYEE", "ADDDATE"}),

  #"Transposed Table" = Table.Transpose(#"Removed Columns"),

  #"Filtered Rows2" = Table.SelectRows(#"Transposed Table", each [Column1] <> 1),

  #"Capitalized Each Word" = Table.TransformColumns(#"Filtered Rows2",{{"Column4", Text.Proper, type text}}),

  #"Added Conditional Column" = Table.AddColumn(#"Capitalized Each Word", "Custom", each if [Column4] = "Cif_Key" then "System Information About Owner" else if [Column4] = "Orig_Person" then "System Information About Account" else null),

  #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Column4] = "~ZZZ~" then "" else null),

  #"Appended query" = Table.Combine({#"Added Conditional Column1", Query2}),

  #"Duplicated Column" = Table.DuplicateColumn(#"Appended query", "Custom.1", "Custom - Copy"),

  #"Added Index" = Table.AddIndexColumn(#"Duplicated Column", "Index", 0, 1, Int64.Type),

  #"Filled Down" = Table.FillDown(#"Added Index", {"Custom - Copy"}),

  #"Reordered columns" = Table.ReorderColumns(#"Filled Down", {"Custom", "Custom.1", "Column1", "Column2"}),

  #"Removed columns 1" = Table.RemoveColumns(#"Reordered columns", {"Column3"}),

  LSTHeaders = Table.ColumnNames(#"Removed columns 1"),

  HowMany = List.Count(LSTHeaders),

  Transformation = Table.TransformColumnTypes(#"Removed columns 1", Table.ToRows(Table.FromColumns({LSTHeaders, List.Repeat({type text}, HowMany )}))),

  #"Removed errors" = Table.RemoveRowsWithErrors(Transformation)

in

  #"Removed errors"

For testing purposes, I intentionally inflated the number of records for the configuration of the dataflow, then when I remove the padded data I get an error about a column name not being in the dataset.

Error: Expression.Error: The column 'Column2819' of the table wasn't found. <ccon>Column2819</ccon>.

the mscipt doesn't explicity call out that column, is it being stored in-memory via the dynamic column type transformation? If I don't have that step power bi adds a transformation to all the columns shown.

[srduval]

Thanks for the response, but that's not the solution. With a reduced column count a refresh will fail. If I open the dataflow in the editor it will run through all the steps with no issue. I can then close and click refresh and it works. I took everything from (including) transpose down and put it into excel and it will dynamically resize without looking for columns that existed during the initial compile. So it's a dataflow/dataset issue. I haven't tired the dataflowgen2 to see if that makes a difference, I was having issues with the untyped columns but now that I might have a waya round that I'll try again.

Edit:

Dataflow Gen2 doesn't work either, all cloud sourced solutions build some sort of static schema and don't grow or shrink with the dataset column wise. Excel works

[SaiTejaTalasila]

Hi,

Please check your applied steps one by one .Instead of removing the columns you can try to filter/select the columns which are required for you.