Multiple Add column

[Gugan_BI]

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

[Anonymous · Accepted solution]

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

[Gugan_BI]

Thanks both.

List.accumulate did a trick. Number of steps for add column reduced from 5 to 1.

[v-alq-msft · Accepted solution]

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 
re

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Gugan_BI]

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]?)

[Anonymous · Accepted solution]

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

[Anonymous]

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]?)