Forum Discussion

Yggdrasill's avatar
Yggdrasill
Responsive Resident
6 years ago
Solved

Google Docs *Community Challenge!*

Hello   As you know the corona virus is spreading around. The information are being gathered publicly here The tables have the same structure for each sheet - which is important ! When I conn...
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Yggdrasill 

     

    You may try to operate as follows.

    1. Connect the url :

    url 

     

    2. Add a custom column

     

    3. Delete the origin Data column

     

    url 

     

    Here is the codes in Advanced Editor.

    let
    Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w/export?format=xlsx&id=1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "DataTable", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded DataTable" = Table.ExpandTableColumn(#"Removed Columns", "DataTable", {"Province/State", "Country/Region", "Last Update", "Confirmed", "Deaths", "Recovered", "Suspected", "Demised", "Country", "Date last updated"}, {"DataTable.Province/State", "DataTable.Country/Region", "DataTable.Last Update", "DataTable.Confirmed", "DataTable.Deaths", "DataTable.Recovered", "DataTable.Suspected", "DataTable.Demised", "DataTable.Country", "DataTable.Date last updated"})
in

     

    Best Regards

    Allan

v-alq-msft's avatar
v-alq-msft
Community Support
6 years ago

Hi, Yggdrasill 

 

You may try to operate as follows.

1. Connect the url :

url 

 

2. Add a custom column

 

3. Delete the origin Data column

 

url 

 

Here is the codes in Advanced Editor.

let
    Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w/export?format=xlsx&id=1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w"), null, true),
    #"Added Custom" = Table.AddColumn(Source, "DataTable", each Table.PromoteHeaders([Data])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded DataTable" = Table.ExpandTableColumn(#"Removed Columns", "DataTable", {"Province/State", "Country/Region", "Last Update", "Confirmed", "Deaths", "Recovered", "Suspected", "Demised", "Country", "Date last updated"}, {"DataTable.Province/State", "DataTable.Country/Region", "DataTable.Last Update", "DataTable.Confirmed", "DataTable.Deaths", "DataTable.Recovered", "DataTable.Suspected", "DataTable.Demised", "DataTable.Country", "DataTable.Date last updated"})
in

 

Best Regards

Allan

Yggdrasill's avatar
Yggdrasill
Responsive Resident
6 years ago

You absolute beauty !

May I ask, how did you get the final url ?

I knew about the /export?format=xlsx but where did you get this string from ?

 

id=1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w

 

EDIT: Nevermind, I see it now it is what appears after /d/  !

https://docs.google.com/spreadsheets/d/1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w/export?format=xlsx&id=1yZv9w9zRKwrGTaR-YzmAqMefw4wMlaXocejdxZaTs6w

 Thanks again !