Forum Discussion
Dynamically set the ColumnName in M query
Hello Everyone,
I have a table where I have to use the table cell value in other table. Here Table Column Name is dynmically changing based on the user input.
Below is the table: [TableName: Title]
I have a code below working fine which is static.
= List.Accumulate
(
{0..Table.RowCount(Title)-1},
#"Changed Type1",
(old,current)=>
Table.AddColumn(old, Title[Title.Name]{current}, each Title[Value.Attributes.L01]{current}, type text)
)
But I would like to pass L01 as dynamic. Based on the user input the value may change. Accordingly table name will also be changed. For example: When user input as L02, then table column name changed to Value.Attributes.L02. Here I have to concatenate in Title[Value.Attributes.L02]{current}
Is it possible to achieve? Thanks in advance
Hi,
I could able to slove the problem by renaming the Column as "Axes" in table as Static in one step above.
#"List Accumulate" = List.Accumulate
(
{0..Table.RowCount(#"TitleDim")-1},
#"Changed Type1",
(old,current)=>
Table.AddColumn(old, #"TitleDim"[Title.Name]{current}, each #"TitleDim"[Axes]{current}, type text)
),
4 Replies
Hi, Anonymous
I'd like to suggest you use 'Manage Parameter' to change column name dynamically. I created data to reproduce your scenario.
You may go to 'Home' ribbon, click 'Manage Parameter' and set as below.
Then you may go to 'Home' ribbon, click 'Advanced Editor', add a 'RenameColumn' step like follows. You need to use the parameter name to replace the value.
Finally you may changed the value of 'ColumnName' parameter to control which column name it displays.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
This can probably be done using #section which creates a list of all queries (list, table, custom function, etc.) but what is your data source?
Hello danextian and v-alq-msft ,
Thanks for your reply. I have a Power Query (M formula) which has to support and work for other attributes too.
My datasource is IBM TM1, using Tm1 Rest api I am pulling the data.
I want to make my code more dynamic and convert to custom connector. I want to change the attribute in the M code based on the user input and tried the below, but its not working. Userinput could be L01, L02, L03 or some other values too.
ColName = "Value.Attributes."&L01&"", then I passed to
#"List Accumulate" = List.Accumulate
(
{0..Table.RowCount(#"TitleDim")-1},
#"Changed Type1",
(old,current)=>
Table.AddColumn(old, #"TitleDim"[Title.Name]{current}, each #"TitleDim"[ColName]{current}, type text)
),how to achieve this? Is it Possible?
Hi,
I could able to slove the problem by renaming the Column as "Axes" in table as Static in one step above.
#"List Accumulate" = List.Accumulate
(
{0..Table.RowCount(#"TitleDim")-1},
#"Changed Type1",
(old,current)=>
Table.AddColumn(old, #"TitleDim"[Title.Name]{current}, each #"TitleDim"[Axes]{current}, type text)
),
