Importing and merging multiple Google BigQuery tables

[Anonymous]

Hi all,

My client's data is automatically recorded into google big query. Every day a new table is created with a datestamp in the same format. I am importing using import mode.

E.g. Todays table would be called Day_20190204

And the M import code is:

Day_20190204_Table = DB_Schema{[Name="Day_20190204",Kind="Table"]}[Data]

I wish to be able to write an M script to automatically import a certain number of days. E.g. Import today + the previous 30 days.

In R I would use the loop and paste functions. Is there something similar in M?

Is this possible?

Many thanks,

Matt

[v-yuezhe-msft · Accepted solution]

@Anonymous,

A method is to create a function in your PBIX file, then create a table containing all the date number of today + the previous 30 days, and invoke the function in this table. There is a similar video for your reference.

Day_20190204_Table = DB_Schema{[Name="Day_"&Number.ToText(date)&"",Kind="Table"]}[Data]

And you can check the M code in my scenario of the function.

(date as number)=>

let
    Source = GoogleBigQuery.Database(null),
    #"bigquery-public-data" = Source{[Name="bigquery-public-data"]}[Data],
    austin_311_Schema = #"bigquery-public-data"{[Name="austin_"&Number.ToText(date)&"",Kind="Schema"]}[Data],
    #"311_service_requests_Table" = austin_311_Schema{[Name="311_service_requests",Kind="Table"]}[Data]
in
    #"311_service_requests_Table"

Regards,
Lydia