Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Team,
I am pulling data from a sharepoint folder that is containing csv files. After pulling data from csv, many changes are done - such as rename, copy, spliting etc.
Now an extra column has been added to the files. Sharepoint the new column is visible but not in power bi.
Here is the code from Advance Editor:
let
Source = SharePoint.Files("https://xxxxx.sharepoint.com/sites/WebAnalyticsTeam", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://xxxxx.sharepoint.com/sites/WebAnalyticsTeam/Shared Documents/Reporting and Dashboards/Power BI Dashboards/Support Dashboard/Product Pages/")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (7)", each #"Transform File (7)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (7)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (7)", Table.ColumnNames(#"Transform File (7)"(#"Sample File (7)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Date", type date}, {"URL (v1) (evar1)", type text}, {"Visits", Int64.Type}, {"Unique Visitors", Int64.Type}, {"Page Views", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each true),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"URL (v1) (evar1)", "URL"}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "URL", "URL - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "URL - Copy", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"URL - Copy.1", "URL - Copy.2", "URL - Copy.3", "URL - Copy.4", "URL - Copy.5", "URL - Copy.6", "URL - Copy.7", "URL - Copy.8", "URL - Copy.9", "URL - Copy.10"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"URL - Copy.1", type text}, {"URL - Copy.2", type text}, {"URL - Copy.3", type text}, {"URL - Copy.4", type text}, {"URL - Copy.5", type text}, {"URL - Copy.6", type text}, {"URL - Copy.7", type text}, {"URL - Copy.8", type text}, {"URL - Copy.9", type text}, {"URL - Copy.10", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"URL - Copy.1", "URL - Copy.2"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each true),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows2",".html","",Replacer.ReplaceText,{"URL - Copy.7"})
in
#"Replaced Value"
Any suggestions?
Don't use the built in file combiner. Create your own via Table.AddColumns, remove the hardcoded number of column and use Table.ColumnNames instead of the hardcoded list.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |