Power BI Data Flow Refreshing issue when change the column of the excel file located in SharePoint

[hsampath81]

Loading data from excel file whare located in SharePoint but Number of columns are uneven

Excel sheet 1

Excel Sheet 2

City 1 is missing of the Excel sheet 2 

I am loading this excel sheet to Power BI Dataflow using following M Queries

let

  Source = SharePoint.Files("https://Abcroup.sharepoint.com/teams/DataVisualizationandDashboards519-HandoverfromSanduni", [ApiVersion = 15]),

  #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Sample")),

  #"Filtered hidden files" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),

  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file (7)", each #"Transform file (7)"([Content])),

  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),

  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file (7)"}),

  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file (7)", Table.ColumnNames(#"Transform file (7)"(#"Sample file (7)"))),

  #"Transform columns" = Table.TransformColumnTypes(#"Expanded table column", {{"ID", type text}, {"Description", type text}, {"Qty", type text}, {"Value", type text}, {"Date", type text}, {"City1", type text}, {"City2", type text}}),

  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"ID", null}, {"Description", null}, {"Qty", null}, {"Value", null}, {"Date", null}, {"City1", null}, {"City2", null}})

in

  #"Replace errors"

But  when it is refresh it give an error

How do we refresh Power BI Data flow from the dynamic Datasource(Number of column change of the excel sheet)

Regards,

Harsha

[ppm1]

The two last steps specifically reference the City1 column. This updated query should avoid that.

let
    Source = SharePoint.Files(
        "https://Abcroup.sharepoint.com/teams/DataVisualizationandDashboards519-HandoverfromSanduni",
        [
            ApiVersion = 15
        ]
    ),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Sample")),
    #"Filtered hidden files" = Table.SelectRows(#"Filtered Rows", each[Attributes]?[Hidden]? <> true),
    #"Invoke custom function" = Table.AddColumn(
        #"Filtered hidden files", "Transform file (7)", each #"Transform file (7)"([Content])
    ),
    #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),
    #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file (7)"}),
    #"Expanded table column" = Table.ExpandTableColumn(
        #"Removed other columns", "Transform file (7)", Table.ColumnNames(#"Transform file (7)"(#"Sample file (7)"))
    ),
    #"Transform columns" = Table.TransformColumnTypes(
        #"Expanded table column",
        {
            {"ID", type text},
            {"Description", type text},
            {"Qty", type text},
            {"Value", type text},
            {"Date", type text},
            {"City1", type text},
            {"City2", type text}
        },
        MissingField.Ignore
    ),
    #"Replace errors" = Table.ReplaceErrorValues(
        #"Transform columns", List.Transform(Table.ColumnNames(#"Transform columns"), each {_, null})
    )
in
    #"Replace errors"

Pat

[hsampath81]

I tried above Code

it give an following error

 Harsha