Forum Discussion

hsampath81's avatar
hsampath81
Frequent Visitor
3 years ago

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

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
  #"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

 

2 Replies

  • ppm1's avatar
    ppm1
    Solution Sage

    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's avatar
      hsampath81
      Frequent Visitor

      I tried above Code

       

      it give an following error

       

       Harsha