Forum Discussion

Apple08's avatar
Apple08
Helper IV
4 years ago
Solved

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...
  • nandukrishnavs's avatar
    4 years ago

    Apple08 

     

    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's avatar
    Apple08
    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?