Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PbiCeo
Helper II
Helper II

Loading a new file on SPO without re-publish

Hello everyone,

 

I publish reports from PBI desktop to PBI service getting csv files on multiple SPO sites, specifying a folder and take a file in a subfolder. A new file will be added in specific timing, so I want to update the data of the report already published on PBI service without taking a new file on PBI desktop and re-publish it.
Is there any way? In that case can I use the report already created on PBI desktop without creating a new report from scratch?

 

1 ACCEPTED SOLUTION

Here is an example that grabs CSV files from a sharepoint folder. The files are sufficiently similar but I also use a custom function (#"Get CSV File") to trim them down to what I need.

 

You also need a list of columns if you want to avoid having to read the first file twice (so don't repeat my mistakes 🙂 )

 

 

 

let
    Source = SharePoint.Files("https://xxx.sharepoint.com/sites/yyy", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://xxx.sharepoint.com/sites/yyy/Shared Documents/") and ([Extension] = ".csv") and Text.StartsWith([Name], "zzz")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows", {"Name", "Source.Name"}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Renamed Columns1", "CSV", each #"Get CSV file"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Source.Name", "CSV","Date modified"}),
    L = Table.ColumnNames(#"Removed Other Columns1"{0}[CSV]),
    #"Expanded CSV" = Table.ExpandTableColumn(#"Removed Other Columns1", "CSV", L,null)
in
    #"Expanded CSV"

 

 

 

The custom function is like this:

 

 

 

(File) => let
        Source = Csv.Document(File,[Delimiter=",",  Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{<list of columns you actually need>})
    in
        #"Removed Other Columns"

 

 

Note that I specifically removed the columns parameter so that the code doesn't break when the number of columns changes.

 

 

 

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

Yes, in your Power Query you use the Sharepoint.Files connector (called Sharepoint Folder in the UI)  rather than the web.contents connector.

 

Then you can write Power Query code that loads and concatenates all CSV files in a particular folder (assuming they all have the same structure, or are close enough)

 

That is pretty standard Power Query code. I can show some examples if needed.

Thank you for your quick reply.

Yes, please show me a PQ sample.

Vladi

Here is an example that grabs CSV files from a sharepoint folder. The files are sufficiently similar but I also use a custom function (#"Get CSV File") to trim them down to what I need.

 

You also need a list of columns if you want to avoid having to read the first file twice (so don't repeat my mistakes 🙂 )

 

 

 

let
    Source = SharePoint.Files("https://xxx.sharepoint.com/sites/yyy", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://xxx.sharepoint.com/sites/yyy/Shared Documents/") and ([Extension] = ".csv") and Text.StartsWith([Name], "zzz")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows", {"Name", "Source.Name"}),
    #"Invoke Custom Function1" = Table.AddColumn(#"Renamed Columns1", "CSV", each #"Get CSV file"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Source.Name", "CSV","Date modified"}),
    L = Table.ColumnNames(#"Removed Other Columns1"{0}[CSV]),
    #"Expanded CSV" = Table.ExpandTableColumn(#"Removed Other Columns1", "CSV", L,null)
in
    #"Expanded CSV"

 

 

 

The custom function is like this:

 

 

 

(File) => let
        Source = Csv.Document(File,[Delimiter=",",  Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{<list of columns you actually need>})
    in
        #"Removed Other Columns"

 

 

Note that I specifically removed the columns parameter so that the code doesn't break when the number of columns changes.

 

 

 

By the way, is there any way to do that just with GUI? (not with PQ)

Now I use manual refresh or schedule refresh on PBI service,

but it doesn't update data even a new file is added to SPO folder.

No, the GUI insists on creating the helper functions and samples.  Needs to be done in Power Query.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors