Forum Discussion
Connect to SharePoint folder by parameters
- 4 years ago
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" - 4 years ago
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?
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?
- nandukrishnavs4 years agoCommunity 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.