Forum Discussion

Apple08's avatar
Apple08
Icon for Helper IV rankHelper 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 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"

 

 

 

  • 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?

4 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion

    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
      Icon for Helper IV rankHelper 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's avatar
        nandukrishnavs
        Icon for Community Champion rankCommunity 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. 

  • Thanks, I have now updated the PR parameter and it works perfectly.  Many thanks for your help.  🙂