Forum Discussion
Connect to SharePoint folder by parameters
Hi All
I tried to connect my Power BI data source to a SharePoint folder which store the monthly reports, I will add in a new report each month. I tried to use parameters to connect the Power BI with Sharepoint which I assume this is the correct way to do the connection.
I have created two parameters:
Site is the Sharepoint site location
PR is the folder path
I have edited the first two rows of the query, however it keeps telling me 'Token Comma expected' error.... And also a red colour is shown on the right scroll bar at the row below:
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Source.Name", "Date"}}),
Please could anyone give me advice how would I update the code to make it works? Many thanks for your help in advance.
let
Source = SharePoint.Files(Site, [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension]=".xlsx"and([Folder Path]= @Site&@PR)),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Filtered Rows2" = Table.SelectRows(#"Expanded Table Column1", each true),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Source.Name", "Date"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Report","",Replacer.ReplaceText,{"Date"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".xlsx","",Replacer.ReplaceText,{"Date"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type date}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each true)
in
#"Filtered Rows1"
I think you missed a closing bracket ")" in line number 3.
Try this
let Source = SharePoint.Files(Site, [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension]=".xlsx"and([Folder Path]= @Site&@PR))), #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))), #"Filtered Rows2" = Table.SelectRows(#"Expanded Table Column1", each true), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Source.Name", "Date"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Report","",Replacer.ReplaceText,{"Date"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".xlsx","",Replacer.ReplaceText,{"Date"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type date}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each true) in #"Filtered Rows1"Thanks, it resolved the comma issue. However no data are shown after the filters. I have removed all filters and leaving the Souce data on, it showed all files in the SharePoint site instead of going into the folder path. I put the PR parameter as: Documents/Report/PR Folder, I wonder have I put the paramenter incorrectly?
4 Replies
- nandukrishnavs
Community Champion
I think you missed a closing bracket ")" in line number 3.
Try this
let Source = SharePoint.Files(Site, [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension]=".xlsx"and([Folder Path]= @Site&@PR))), #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))), #"Filtered Rows2" = Table.SelectRows(#"Expanded Table Column1", each true), #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Source.Name", "Date"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Report","",Replacer.ReplaceText,{"Date"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".xlsx","",Replacer.ReplaceText,{"Date"}), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Date", type date}}), #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each true) in #"Filtered Rows1"- Apple08
Helper IV
Thanks, it resolved the comma issue. However no data are shown after the filters. I have removed all filters and leaving the Souce data on, it showed all files in the SharePoint site instead of going into the folder path. I put the PR parameter as: Documents/Report/PR Folder, I wonder have I put the paramenter incorrectly?
- nandukrishnavs
Community Champion
Apple08 please validate the folder path. I could see you have concatenated the variables Site and PR. Ensure the output of this concatenation is correct.
- Apple08
Helper IV
Thanks, I have now updated the PR parameter and it works perfectly. Many thanks for your help. 🙂