Power Query - changing Header Title in WebQuery drives me crazy

HolyExcel

Board Regular
Joined
Jul 27, 2014
Messages
65
Hey guys and girls,

great Forum - already learned a lot from all of you.

I have a tricky question concerning WebQuery where I already tried a lot but failed miserably. :( Now I'm hoping you might have got some clues on how to handle that.

A Table on a website gives me Products and Prices. Depending on the day I query that Webpage, the title for the columns where the Products are listed changes. Sadly it contains the information how many lines actually are in the table. So on one day the columnheader could be "37 Products" and on the next day "39 Products" - in the same URL.... :(

As I want to have an excelfile where I grab the data everyday and expand the table with certain Formulas, I get the Problem, that the Tablecolumn itself of course changes too. Is there a way in Power Query to set a column to a certain Name without static columnname? Because if I rename a column it looks like this: Table.RenameColumns(#"Geänderter Typ",{{"37 Products", "Products"}}). Is there a way to avoid this static "37 Products" and replace it with a columnnumber?? Or is there any other option like inserting a new line as columnheader?

Hope I explained it well enough and there are some hints. I would be very greatfull indeed.

BR
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi,
there's a hidden treasure, called "demote headers" that will solve your problem:

HTML:
let
    Quelle = Web.Page(Web.Contents("http://www.boerse.de/technische-analyse/Daimler-Aktie/DE0007100000")),
    Data2 = Quelle{2}[Data],
    // Move header to 1st line of data    
    Ü2 = Table.DemoteHeaders(Data2),
    // Remove header from data
    Import = Table.Skip(Ü2,1)
in
   Import

Just paste this to your advanced editor and see what it does - enjoy :)

Imke
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top