cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattlawrence2 Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Importing and merging multiple Google BigQuery tables

@mattlawrence2,

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"

Englishversion.png

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Importing and merging multiple Google BigQuery tables

@mattlawrence2,

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"

Englishversion.png

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.