Forum Discussion
Multiple Add column
Hi,
Currently working on power bi solution which connects to dynamics 365. There are few optionset fields that need to be converted to actual values. I use the below query to get actual value of optionset.
=Table.addcolumn(#"add custom","label", each value.metadata([optionsetid])[OData.Community.Display.V1.FormattedValue]?)
I've around 5 optionset columns. Finally it ends up five add column steps for each optionset.
Just checking is there option to perform in single step. To see if I can minimize the number of steps.
Thank you
ok.
Then you have to generate/construct the somethinkID list.
You can create manually a list:
lstID={productid,statusid,responseid} or select in somw way from column names list
lstID= List.Select(Table.ColumnNames(tbl), each Text.EndsWith(_,"id"))
Then you have to generate/construct in similar way a labelList.
lstLabel={l1,l2,l3}
Finally you could use list accumulate in the way I showed you.
ASs I don't know the value.metadata function
and MS explanation is very succint
I try to understand the way you used it.
You should use, i think, in this way inside Table.addcolumn funcion:
List.accumulate({0..2},tbl, (s,c)=>List.AddColumn(s, listLabel{c}, each value.metadata(table.Column(s, lstID{c})) )
check the sintax and try.
I don't have time now to go more in depth
Hi, Gugan_BI
As is suggested by Gugan_BI , you may use 'Table.AddColumn' function within 'List.Accumulate' function. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Then you may add a new step with the following m codes.
= let names = Table.ColumnNames(#"Changed Type"), list = List.Select(names,each Text.EndsWith(_,"id")), re = List.Accumulate( list, #"Changed Type", (s,c)=>Table.AddColumn(s,c&"_label",each Value.Metadata( Table.Column(#"Changed Type",c) )[OData.Community.Display.V1.FormattedValue]?) ) in reResult:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
the details you are referring to are not clear to me, but maybe I understand what your need is. So I offer you an idea in very general terms. See for yourself if you can apply it to your case.
Try using somethink similar to
listOtionSet
listLabel
range={0..List.Count(listOptionSet)}
List.Accumulate (range, table, (s, c) => table.AddColumn (s, each listLabel{c}, value.metadata (/*to be dereferenced*/[listOptionSet{c}])[OData.Community.Display.V1.FormattedValue]?)
Thanks for response.
Given below more details. Fundid, statusid and response id are id values (some system generate id from Dynamics)
using below query will give the label/description of each id column.
value.metadata([statusid])[OData.Community.Display.V1.FormattedValue]?
created 3 new columns to get actual description of id values. Product_lablel, Status_label and response_label are new column that contains the description of id values.
so trying to see if i can combine the below three steps into minimal steps. I may have more such id columns.
#"add custom" =Table.addcolumn(table1,"product_label", each value.metadata([productid])[OData.Community.Display.V1.FormattedValue]?)
#"add custom1" =Table.addcolumn(#"add custom","Status_label", each value.metadata([statusid])[OData.Community.Display.V1.FormattedValue]?)
#"add custom2" =Table.addcolumn(#"add custom1","response_label", each value.metadata([responseid])[OData.Community.Display.V1.FormattedValue]?)
ok.
Then you have to generate/construct the somethinkID list.
You can create manually a list:
lstID={productid,statusid,responseid} or select in somw way from column names list
lstID= List.Select(Table.ColumnNames(tbl), each Text.EndsWith(_,"id"))
Then you have to generate/construct in similar way a labelList.
lstLabel={l1,l2,l3}
Finally you could use list accumulate in the way I showed you.
ASs I don't know the value.metadata function
and MS explanation is very succint
I try to understand the way you used it.
You should use, i think, in this way inside Table.addcolumn funcion:
List.accumulate({0..2},tbl, (s,c)=>List.AddColumn(s, listLabel{c}, each value.metadata(table.Column(s, lstID{c})) )
check the sintax and try.
I don't have time now to go more in depth
Hi, Gugan_BI
As is suggested by Gugan_BI , you may use 'Table.AddColumn' function within 'List.Accumulate' function. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Then you may add a new step with the following m codes.
= let names = Table.ColumnNames(#"Changed Type"), list = List.Select(names,each Text.EndsWith(_,"id")), re = List.Accumulate( list, #"Changed Type", (s,c)=>Table.AddColumn(s,c&"_label",each Value.Metadata( Table.Column(#"Changed Type",c) )[OData.Community.Display.V1.FormattedValue]?) ) in reResult:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks both.
List.accumulate did a trick. Number of steps for add column reduced from 5 to 1.
