Forum Discussion
hsampath81
3 years agoFrequent Visitor
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
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
2 Replies
- ppm1Solution 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
- hsampath81Frequent Visitor
I tried above Code
it give an following error
Harsha