Skip to main content

Power BI

Needs Votes

Please use Parameter1 in the code template created by the "SharePoint Folder" connector

Vote (2) Share
Pär Adeen's profile image

Pär Adeen on 26 May 2021 10:25:10

The code generated when using the "SharePoint Folder" connector looks like below. The change needed is to use Parameter1 instead of the Query name #"Sample File" så that the last line will look like this:
Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(Parameter1)))

let
Source = SharePoint.Files("https://contoso.sharepoint.com/teams/TEAMS1/", [ApiVersion = 15]),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
#"Expanded Table Column1"

Also, Parameter1 is not a very good name, Please rename it to SampleFileParameter or something equal.

Rational:
Using the parameter instead of the real name will
1) Use the parameter, because why create it if not used. Yes it's used in the "Transform Sample File" query, but if used only here, we could like as well have used the query name directly
2) This makes it possible to create a second sample file to switch between. This is something you might want to do having files not looking the same. And changing sample file is not that simple since choosing another index instead of #"Filtered Rows"{0}[Content] in the file navigation will automatically add an extra query step that will fail the whole query, and this is not very easy to spot (this toked some time to understand).
3) Not using Parameter1 (That shall be named something else) you will have an inconsistency between the sample query and what's done in the final query where it comes to the columns expanded