getting data from web

[Anonymous]

hi

i have the following data

http://data.humdata.org/hxlproxy/data/download/time_series_covid19_confirmed_global_iso3_regions.csv...

so the data is in csv and is online, i say great lets  make a dashboard , I start with power query and clean the data and everything work great for one day, but at the next day when the data actualize I have an issue the data set grow by one column and the data set captured by power query does not, so if Monday is 92 column the following day is going to be 93 but power query will bring only 92, because of this I get the error of missing columns.

The last column are about localization and when a new day get add it to the data set they are left out.

[camargos88]

Hi @Anonymous ,

Maybe you are unpivoting using the data ranges ? If yes, when it gets updated some columns are left behind.

I've create this file, let's try it. Download PBIX 

It has a custom column to simulate the data and to unpivot the data.

I hope it helps,

If you consider it as a solution, please mark as solution and kudos.

Ricardo

[Anonymous]

it does not work, yesterday i was able to get the data, today it give an error and i have to get back a few steps, a column was missing

is the same error i get, i still do not know why power query is not able to capture the new columns

so the query today get 95 columns, same number as yesterday, (intermediate region name is the missing column)

when it should get 96 columns for today

so the issue remain, what should i do to make power query grab the last column when update?

[Anonymous]

thanks!

i will give it a try using as a base for the query and the real test is going to be tomorrow when the data update

[v-lid-msft]

Hi @Anonymous ,

We can unpivot other columns outside the constant column to meet your requirement:

let
    Source = Csv.Document(Web.Contents("https://data.humdata.org/hxlproxy/data/download/time_series_covid19_confirmed_global_iso3_regions.csv?dest=data_edit&filter01=merge&merge-url01=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2Fe%2F2PACX-1vTglKQRXpkKSErDiWG6ycqEth32MY0reMuVGhaslImLjfuLU0EUgyyu2e-3vKDArjqGX7dXEBV8FJ4f%2Fpub%3Fgid%3D1326629740%26single%3Dtrue%26output%3Dcsv&merge-keys01=%23country%2Bname&merge-tags01=%23country%2Bcode%2C%23region%2Bmain%2Bcode%2C%23region%2Bmain%2Bname%2C%23region%2Bsub%2Bcode%2C%23region%2Bsub%2Bname%2C%23region%2Bintermediate%2Bcode%2C%23region%2Bintermediate%2Bname&filter02=merge&merge-url02=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2Fe%2F2PACX-1vTglKQRXpkKSErDiWG6ycqEth32MY0reMuVGhaslImLjfuLU0EUgyyu2e-3vKDArjqGX7dXEBV8FJ4f%2Fpub%3Fgid%3D398158223%26single%3Dtrue%26output%3Dcsv&merge-keys02=%23adm1%2Bname&merge-tags02=%23country%2Bcode%2C%23region%2Bmain%2Bcode%2C%23region%2Bmain%2Bname%2C%23region%2Bsub%2Bcode%2C%23region%2Bsub%2Bname%2C%23region%2Bintermediate%2Bcode%2C%23region%2Bintermediate%2Bname&merge-replace02=on&merge-overwrite02=on&tagger-match-all=on&tagger-01-header=province%2Fstate&tagger-01-tag=%23adm1%2Bname&tagger-02-header=country%2Fregion&tagger-02-tag=%23country%2Bname&tagger-03-header=lat&tagger-03-tag=%23geo%2Blat&tagger-04-header=long&tagger-04-tag=%23geo%2Blon&header-row=1&url=https%3A%2F%2Fraw.githubusercontent.com%2FCSSEGISandData%2FCOVID-19%2Fmaster%2Fcsse_covid_19_data%2Fcsse_covid_19_time_series%2Ftime_series_covid19_confirmed_global.csv"),[Delimiter=",", Columns=96, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Top Rows" = Table.Skip(#"Promoted Headers",1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows", {"Province/State", "Country/Region", "Lat", "Long", "ISO 3166-1 Alpha 3-Codes", "Region Code", "Region Name", "Sub-region Code", "Sub-region Name", "Intermediate Region Code", "Intermediate Region Name"}, "Date", "Amount"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Date", type date}, {"Amount", Int64.Type}})
in
    #"Changed Type"

If new columns added , it will show in the Date column:

By the way, PBIX file as attached.

Best regards,

[Anonymous]

is posible to add a formula to calculate the number of column it should bring?

today the correct number is 96 but tomorrow is going to be 97 and the day after that 98 and so on, so 

let
    Source = Csv.Document(Web.Contents("https://data.humdata.org/hxlproxy/data/download/time_series_covid19_confirmed_global_iso3_regions.csv?dest=data_edit&filter01=merge&merge-url01=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2Fe%2F2PACX-1vTglKQRXpkKSErDiWG6ycqEth32MY0reMuVGhaslImLjfuLU0EUgyyu2e-3vKDArjqGX7dXEBV8FJ4f%2Fpub%3Fgid%3D1326629740%26single%3Dtrue%26output%3Dcsv&merge-keys01=%23country%2Bname&merge-tags01=%23country%2Bcode%2C%23region%2Bmain%2Bcode%2C%23region%2Bmain%2Bname%2C%23region%2Bsub%2Bcode%2C%23region%2Bsub%2Bname%2C%23region%2Bintermediate%2Bcode%2C%23region%2Bintermediate%2Bname&filter02=merge&merge-url02=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2Fe%2F2PACX-1vTglKQRXpkKSErDiWG6ycqEth32MY0reMuVGhaslImLjfuLU0EUgyyu2e-3vKDArjqGX7dXEBV8FJ4f%2Fpub%3Fgid%3D398158223%26single%3Dtrue%26output%3Dcsv&merge-keys02=%23adm1%2Bname&merge-tags02=%23country%2Bcode%2C%23region%2Bmain%2Bcode%2C%23region%2Bmain%2Bname%2C%23region%2Bsub%2Bcode%2C%23region%2Bsub%2Bname%2C%23region%2Bintermediate%2Bcode%2C%23region%2Bintermediate%2Bname&merge-replace02=on&merge-overwrite02=on&tagger-match-all=on&tagger-01-header=province%2Fstate&tagger-01-tag=%23adm1%2Bname&tagger-02-header=country%2Fregion&tagger-02-tag=%23country%2Bname&tagger-03-header=lat&tagger-03-tag=%23geo%2Blat&tagger-04-header=long&tagger-04-tag=%23geo%2Blon&header-row=1&url=https%3A%2F%2Fraw.githubusercontent.com%2FCSSEGISandData%2FCOVID-19%2Fmaster%2Fcsse_covid_19_data%2Fcsse_covid_19_time_series%2Ftime_series_covid19_confirmed_global.csv"),[Delimiter=",", Columns=96, Encoding=65001, QuoteStyle=QuoteStyle.None])

the last line :

[Delimiter=",", Columns=96, Encoding=65001, QuoteStyle=QuoteStyle.None])

maybe if that number grow with the day .

[Anonymous · Accepted solution]

let
    Origen = Csv.Document(Web.Contents("https://data.humdata.org/hxlproxy/data/download/time_series_covid19_confirmed_global_iso3_regions.csv?dest=data_edit&filter01=merge&merge-url01=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2Fe%2F2PACX-1vTglKQRXpkKSErDiWG6ycqEth32MY0reMuVGhaslImLjfuLU0EUgyyu2e-3vKDArjqGX7dXEBV8FJ4f%2Fpub%3Fgid%3D1326629740%26single%3Dtrue%26output%3Dcsv&merge-keys01=%23country%2Bname&merge-tags01=%23country%2Bcode%2C%23region%2Bmain%2Bcode%2C%23region%2Bmain%2Bname%2C%23region%2Bsub%2Bcode%2C%23region%2Bsub%2Bname%2C%23region%2Bintermediate%2Bcode%2C%23region%2Bintermediate%2Bname&filter02=merge&merge-url02=https%3A%2F%2Fdocs.google.com%2Fspreadsheets%2Fd%2Fe%2F2PACX-1vTglKQRXpkKSErDiWG6ycqEth32MY0reMuVGhaslImLjfuLU0EUgyyu2e-3vKDArjqGX7dXEBV8FJ4f%2Fpub%3Fgid%3D398158223%26single%3Dtrue%26output%3Dcsv&merge-keys02=%23adm1%2Bname&merge-tags02=%23country%2Bcode%2C%23region%2Bmain%2Bcode%2C%23region%2Bmain%2Bname%2C%23region%2Bsub%2Bcode%2C%23region%2Bsub%2Bname%2C%23region%2Bintermediate%2Bcode%2C%23region%2Bintermediate%2Bname&merge-replace02=on&merge-overwrite02=on&tagger-match-all=on&tagger-01-header=province%2Fstate&tagger-01-tag=%23adm1%2Bname&tagger-02-header=country%2Fregion&tagger-02-tag=%23country%2Bname&tagger-03-header=lat&tagger-03-tag=%23geo%2Blat&tagger-04-header=long&tagger-04-tag=%23geo%2Blon&header-row=1&url=https%3A%2F%2Fraw.githubusercontent.com%2FCSSEGISandData%2FCOVID-19%2Fmaster%2Fcsse_covid_19_data%2Fcsse_covid_19_time_series%2Ftime_series_covid19_confirmed_global.csv"),[Delimiter=",", Columns=Number.RoundDown(Number.From(DateTime.LocalNow()))-43841, Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    Origen

i create a formula that calculate the number of column daily :

Columns=Number.RoundDown(Number.From(DateTime.LocalNow()))-43841

it will grow daily and i hope tomorrow will bring the update without issues

[Anonymous]

i will give it a try now, so i can see if tomorrow work properly, i will let you know