Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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
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
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.
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
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
Okay, that clarify.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.