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 e...
ppm1
3 years agoSolution 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
3 years agoFrequent Visitor
I tried above Code
it give an following error
Harsha