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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
possible-possum
Frequent Visitor

Auto-append Google Sheets worksheets for monthly data

I have a new data source to add into my model that is a Google Sheets document. Each month, a new worksheet is added to the document, so I will have a set of data in an identical column format for Jan 2023, Feb 2023 and so on.

 

I'd love to be able to set it up so that Power Query can automatically append the data from each of the worksheets into a single query, without needing to open up Power Query each month and load the new sheet in as a query and append manually.

 

Does anyone have any ideas of how this could be achieved?

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

You can easily load them all in a single query.

 

Since I don't have your worksheet, here's a Google sheet that has tabs including Week 1 through Week 4:
https://docs.google.com/spreadsheets/d/1z88d0BCV7jDOyPouLc5TzYjAqY9u_p8ZnTmDkaABACE/edit#gid=0

 

When you load it into the query editor, delete all except the first applied step and it should look like this:

AlexisOlson_0-1673560358054.png

 

Filter the [name] column to remove the sheets you don't want and expand the data column... and that's about it other than cleaning up rows and columns you don't want.

 

Here is what the whole query could look like:

let
    Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/1z88d0BCV7jDOyPouLc5TzYjAqY9u_p8ZnTmDkaABACE/edit#gid=0"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([name] <> "Inputs")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Exercise", "Purpose", "Set Scheme", "Weight", "Percentage", "Comments"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each List.Contains({"0".."9"}, Text.Start([Set Scheme],1)))
in
    #"Filtered Rows1"

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

You can easily load them all in a single query.

 

Since I don't have your worksheet, here's a Google sheet that has tabs including Week 1 through Week 4:
https://docs.google.com/spreadsheets/d/1z88d0BCV7jDOyPouLc5TzYjAqY9u_p8ZnTmDkaABACE/edit#gid=0

 

When you load it into the query editor, delete all except the first applied step and it should look like this:

AlexisOlson_0-1673560358054.png

 

Filter the [name] column to remove the sheets you don't want and expand the data column... and that's about it other than cleaning up rows and columns you don't want.

 

Here is what the whole query could look like:

let
    Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/1z88d0BCV7jDOyPouLc5TzYjAqY9u_p8ZnTmDkaABACE/edit#gid=0"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([name] <> "Inputs")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Exercise", "Purpose", "Set Scheme", "Weight", "Percentage", "Comments"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Data", each List.Contains({"0".."9"}, Text.Start([Set Scheme],1)))
in
    #"Filtered Rows1"

This looks like exactly what I need (including the ability to exclude an inputs sheet). I'll try it out and report back!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors