Use First Row as a Header

[Anonymous]

Hello,

Is there any way to replace the string values highlighted in red colour by a value given as an input? The thing is those values varies depending of the retrieved data. I am using them in a function.

#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Price", type text}, {"Rates", type text}, {"Stamp Duty", type text}

Best,

[Jimmy801]

Hello @Anonymous ,

but in witch step you need to have them dynamically?

Meaning when you need them for the Table.TransformColumnTypes you have to read first the column names, create a second list with the types in it, Zip them and then use them as parameter for the function something like this

    Header = Table.ColumnNames(#"Promoted Headers"),
    Type = {type text, type text},
    ZipHeaderType = List.Zip({Header, Type}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",ZipHeaderType)

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

Sorry, I think I did not express myself well. The data for the headers is taken from a series of website tables through a function, some of them just varies in position and value, so headers will change. I just wonder if there is an string expression that I can put between " " to consider whatever value comes in. But nothing about retrieved data. Thanks for your help.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

the code that you posted that Power Query promotes the header and then changes the data type. Whenever you change the data type, you have to indicate column name and type in a nested list. So when the data structure can be changed (column names changed) you have to apply the code I gave you, this works dynamically.

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

I went through your comments with regard nested lists looking for information and trying different things, but I was unable to make it works. Still I missing something in my code.

let
Source = Web.BrowserContents("https://www.propertypal.com/410-falls-road-belfast/608614"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='key-info-table'] > TR > :nth-child(1), TABLE[id='key-info-table'] > * > TR > :nth-child(1)"}, {"Column2", "TABLE[id='key-info-table'] > TR > :nth-child(2), TABLE[id='key-info-table'] > * > TR > :nth-child(2)"}}, [RowSelector="TABLE[id='key-info-table'] > TR, TABLE[id='key-info-table'] > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Header", type text}}),
Header = Table.ColumnNames(#"Promoted Headers"),
Type = {type text, type text},
ZipHeaderType = List.Zip({Header, Type}),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",ZipHeaderType)

in
#"Changed Type2"

Can you please give me a hand with it?

Regards

[Anonymous]

Okay, that clarify.