Auto-append Google Sheets worksheets for monthly data

[Anonymous]

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?

[AlexisOlson · Accepted solution]

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:

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"

[Anonymous]

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