Forum Discussion
getting data from web
hi
i have the following data
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.
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 Origeni 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
7 Replies
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
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,
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?
