Forum Discussion
Importing and merging multiple Google BigQuery tables
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
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
1 Reply
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