Power Query Web data - credentials and changing tables

[masterelaichi]

Hi,

Hope someone can help me out. I am trying to achieve two things but not sure how to go about it -

1. Import data into PQ from a website that has tables that change depending on what option is selected
The website in question is https://qsuper.qld.gov.au/performance/compare-unit-prices/

The tables show different values depending on an Income account or Accumulation Account. I want to load both these tables into PQ but unable to do so

2. Import data from a table that requires login credentials

Is this possible in PQ? Eg- importing banking transactions etc

Thanks

 

[masterelaichi]

Sorry to bump this as I have another thing to add to it...

Is it possible to retain the table imported from a website?

Bit of an explanation here...the link in my previous post gets updated daily with unit prices for that day. But at the start of a new month, the website refreshes and starts again from the 1st of the month thereby erasing the previous month's data from my excel tables

Is it possilbe to append the new data to the old one without losing the information for the previous month?

Thanks in advance

 

[ImkeF]

1) If the parameters are not part of the URL (like in your example), it almost/most often impossible
2) Generally possible. You need to find the right that prompts you to enter your password - all needs to be done through the UI.
3) Check out this article: Self Referencing Tables in Power Query - Excelerator BI it includes all techniques you need to perform an incremental load: Instead of adding a column, perform an append (Table.Combine) on the self-referenced table.

 

[masterelaichi]

1) If the parameters are not part of the URL (like in your example), it almost/most often impossible
2) Generally possible. You need to find the right that prompts you to enter your password - all needs to be done through the UI.
3) Check out this article: Self Referencing Tables in Power Query - Excelerator BI it includes all techniques you need to perform an incremental load: Instead of adding a column, perform an append (Table.Combine) on the self-referenced table.

Hi,

Thanks for your reply. I don't quire follow. IN your post you added a datetime column. However, you suggest appending a table.combine to the self-referenced table. Does this mean I don't have to create a duplicate query?

 

[ImkeF]

Sorry, yes, this technique can be a bit confusing.

1) First create a query that imports from the website and name it "ExistingData". (Load option: ToTable)
2) Duplicate this query and name it "NewData". (Load option: ConnectionOnly)
3) Edit query "ExistingData" (as you don't want to import your source 2 times): Instead of getting data from the web, it shall reference (just import) the newly created Excel-Table "ExistingData".
4) Append query "NewData".

You will need to find a suitable filter in query "NewData" that avoids duplicate imports during the month. Therefore you either check for the latest date in the ExitingData (or simply use brute force and import everything and remove duplicates after the import )

 

[masterelaichi]

ImkeF,

I tried implementing your technique. I just have to wait until October now to see if it work with automatic data refresh. Hopefully it works :D

I don't quite understand the difference between "Duplicate Query" and "Reference" in PQ though

Thanks

 

[ImkeF]

If you reference a query or a table you treat it as a variable. Basically saying:"Whatever your query-steps (or table) return, pass it on to me". So you're just taking the result.
If you duplicate a query you clone it. Basically saying: "Whatever you're doing query (in all of your single steps) - I will do just the same". So you're basically copying an action plan.

If you rightclick on a query name, you have these 2 options. Try them out and you will see that "Reference" will always only return one row of code: Source=NameOfReferencedQuery
"Duplicate" on the other hand will return the (whole) copied code from the original query.

Hope this makes sense?

 

[masterelaichi]

Thanks for your help with this ImkeF

To be honest, I still cannot figure this one out! I just don't know why it doesn't seem to work for me. I created a test workbook with some dummy values. What happens is that the existing data gets replaced with the new one

I tried following the steps in your blog How to create a Load History or Load Log in Power Query or Power BI – The BIccountant. For some reason I am not getting Step 5, i.e that "Added Custom". It is just not there in my list of steps

Any suggestions?

Thanks

 

[ImkeF]

Sorry to hear.
Will make screen recording over the weekend.

 

[masterelaichi]

@Imkef

That will be very helpful. Thank you...