Forum Discussion
Power Query, for every column, use a function that takes the column name to fill the rows
Hi,
I have an empty table in power query with only column names. The column names are retrieved from an API which is the list of all parameters that can be used with the API. I want to fill the rows for each column where it will take the column name, use that as the API parameter and fill the rows.
Any samples someone can share?
The simplest approach would be to get the list of column names, then build a table off that. Then you can use one of the Add Columns options to execute your API
To get the columns into a list you can call the Table.ColumnNames function on your existing table. In the example code below I added the "step1" step in the Advanced Editor view to get the list of column names. Then I closed the Advanced Editor and clicked the "To Table" button to generate the final #"Converted to Table" step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}), step1 = Table.ColumnNames(#"Changed Type"), #"Converted to Table" = Table.FromList(step1, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"If you create a blank query, then switch to the Advanced Editor and paste in the text above you can see how it works.
1 Reply
The simplest approach would be to get the list of column names, then build a table off that. Then you can use one of the Add Columns options to execute your API
To get the columns into a list you can call the Table.ColumnNames function on your existing table. In the example code below I added the "step1" step in the Advanced Editor view to get the list of column names. Then I closed the Advanced Editor and clicked the "To Table" button to generate the final #"Converted to Table" step.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}), step1 = Table.ColumnNames(#"Changed Type"), #"Converted to Table" = Table.FromList(step1, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"If you create a blank query, then switch to the Advanced Editor and paste in the text above you can see how it works.