Auto adding column from Web/HTML table

[Anonymous]

So I have a report that reads a table from a web source, and every now and then it updates with another column, which I then want to appear when I refresh data in Power BI.

Right now when I hit refresh it just updates the current rows and columns, but doesn't add the new column. Is there any way to automate this without me having to manually go into the advanced editor in the Transform data tab?

Thanks

[andrew_bouland]

Apologies for posting to an old thread but was having similar issues and found plenty of suggestions when the data source is csv file and there is a Column number statement in the advanced editor.  When retrieving data from a web source, this Column number may not be specified in the Advanced Editor though.

This post helped me find my problem: https://powerusers.microsoft.com/t5/Power-Query/Missing-column-when-refreshing/td-p/1072968

In Advanced Editor under Query Editor, at the top, look for section re: #"Expanded records1" = Table.ExpandRecordColumn(#"Expanded records", "records", {".....  There are two sections within this line and you need to add your new column(s) to both.  If you only add to bottom or top section then you'll get an error re: Expression.Error: We expected newColumnNames to have the same number of items as fieldNames.
Details:
[List]

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Yes, I reproduced your problem. Web data sources do have this problem.

The reason for this problem is that the second parameter of Html.Table() is hard-coded.

So I recommend using Python scripts in Power Query to get web data source data.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[negi007]

@Anonymous Can you try below links for help.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-tutorial-importing-and-analyzing-data-from-a-web-page

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-to-web-by-example

https://docs.microsoft.com/en-us/power-query/connectors/web/web#:~:text=request%20header%20parameters-,Load%20Web%20data%20using%20Power%20Query%20Desktop,address%20in%20the%20text%20box.

[Anonymous]

Thanks for the reply, 

But I couldn't really see anything that help, maybe I missed something.

The problem isn't connecting and retrieving + transforming data.

The "problem" is when I that table updates and adds another column. And then doesn't appear in Power BI. Atleast not what I can see. 

So maybe there's something in advanced editor or something that can be done to auto add new columns?

Thanks 

[amitchandak]

@Anonymous , Check if power bi cmdlets, can help

https://powerbi.microsoft.com/en-us/blog/working-with-powershell-in-power-bi/

[Anonymous]

Thanks for the reply, but unfortunately for this I can't rely on Power shell. And I didn't see anything about auto adding columns from web source that has already been imported and transformed? Maybe I misread 

thanks