cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MJimenez Frequent Visitor
Frequent Visitor

Use First Row as a Header

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Jimmy801 New Contributor
New Contributor

Re: Use First Row as a Header

Hello @MJimenez 

 

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

View solution in original post

5 REPLIES 5
Jimmy801 New Contributor
New Contributor

Re: Use First Row as a Header

Hello @MJimenez ,

 

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

MJimenez Frequent Visitor
Frequent Visitor

Re: Use First Row as a Header

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 New Contributor
New Contributor

Re: Use First Row as a Header

Hello @MJimenez 

 

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

View solution in original post

MJimenez Frequent Visitor
Frequent Visitor

Re: Use First Row as a Header

Okay, that clarify.

MJimenez Frequent Visitor
Frequent Visitor

Re: Use First Row as a Header

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

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)