topic Re: List.Transform list of columns with return value with function which needs the actual columnName in Dataflow

List.Transform list of columns with return value with function which needs the actual columnName

mrc_bob — Wed, 09 Aug 2023 15:30:12 GMT

I am trying for a while to have a dynamic step to transform the data from 1-n columns with the result of a function call.

We are working on Dynamics 365 where we have a lot of optionsets.

I have a table with all optionsets and a step where I join my columnnames to this table. So I know which columns contain an optionset value.

I have created a function which delivers the right label for the optionset value.

OptionLookup(opstionsetvalue, "entityname", "optionsetname(columnname)")

So basically I created a step:

Table.TransformColumns(DateToLocal,List.Transform(OptionsetColumns,

each { , each OptionLookup(, "bookableresource", "here should the columnName be as text"), type text}))

the _ provides the right current value needed for the function, the function is also applied to the right columns but the function needs the ColumnName in text and I don't know how to get this.

I tried OptionsetColumns but then the whole list is inserted there.

Re: List.Transform list of columns with return value with function which needs the actual columnName

miguel — Wed, 09 Aug 2023 16:17:24 GMT

Hey!

Is there any way that you can share a functional example M code that contains that list and what you're trying to achieve? wondering how your list actually looks like

Re: List.Transform list of columns with return value with function which needs the actual columnName

DennesTorres — Wed, 09 Aug 2023 16:24:47 GMT

Hi,

I will give some guesses, I hope it provides some ideas to you, sorry in advance if it's a complete miss.

You are trying, inside two level of "each" in power query, to retrieve metadata from the collection, in this case, the columnname. If Power Query had the function NameOf() maybe this would be perfect for what you want, but I think it hasn't yet, it's only in DAX.

Did you thought about turn this logic upside down ? The Table.ColumnNames can retrieve the name of the columns, maybe you could loop through it to retrieve the value of the columns for each row, already having the columnnames in hand?

Kind Regards,

Dennes

Re: List.Transform list of columns with return value with function which needs the actual columnName

mrc_bob — Wed, 09 Aug 2023 16:31:48 GMT

I have one step with Table.ColumnNames which contains f.e. 3 columnsNames resourcetype, msdyn_startlocation,usertype.
This step is called OptionsetColumns and used in the List.Transform.
the each _ gives the value of the column f.e.669010000.
which I need as well the provide as the 1st parameter to the function but need the columns name like resourcetype as well.

I will post some more details later. Now on my phone....

Re: List.Transform list of columns with return value with function which needs the actual columnName

DennesTorres — Wed, 09 Aug 2023 16:47:21 GMT

Hi,

So, once again, I may be able to provide some guesses and hope they help:

If OptionsetColumns is already an array of columnNames, such as {"CustomerID", "Name", "Phone"},
the "_" on the first each, the 2n parameter of the List.Transform, is exactly the column name you want to retrieve.

For example, just to have a better view of the code, let me show a silly example:

List.Transform({"CustomerID", "Name", "Phone"}, each _ + "column")

May result in {"CustomerIDcolumn", "Namecolumn", "Phonecolumn"},

But instead of the array you have OptionsetColumns, but it's the same. The "_" on the first each has the column name and in this case I don't fully understand how your 2nd each is working...

Kind Regards,

Dennes

Re: List.Transform list of columns with return value with function which needs the actual columnName

mrc_bob — Wed, 09 Aug 2023 20:23:18 GMT

So for this first each I had the idea already this is the column name.

within my function I need the value of the record within that column, "static entity name", "column name"

I beleave the second each gives me the value correct. But how do I get the column name of the first each again?

Re: List.Transform list of columns with return value with function which needs the actual columnName

DennesTorres — Wed, 09 Aug 2023 20:33:04 GMT

Hi,

Sorry, I didn't get your 2nd each.

The first each is inside the List.Transform. The List.Transform is applied over a single dimension array. The "_" is a string, the column name from the single dimension array.

I didn't get what iteration the 2nd each is trying to do. What the "_" on the 2nd each is ? Does the 2nd each works, if you make a more simple transformation, without the function call ?

Kind Regards,

Dennes

Re: List.Transform list of columns with return value with function which needs the actual columnName

mrc_bob — Thu, 10 Aug 2023 07:48:54 GMT

So my Complete code for one source:

let

Bron = Sql.Database("synapse-365-ondemand.sql.azuresynapse.net", "dataverse_**********"),

DB = Value.NativeQuery(Bron,"select bookableresourceid,createdon,modifiedon,createdbyname,modifiedbyname,owninguser,name,calendarid,contactid,resourcetype,statecode,statuscode,userid,contactidname,useridname,msdyn_organizationalunit,msdyn_startlocation,msdyn_targetutilization,msdyn_organizationalunitname,msdyn_facilityequipmentidname,msdyn_hourlyrate from bookableresource" , null, [EnableFolding=true]),

DateToLocal = Table.TransformColumns(DB, List.Transform(Table.ColumnsOfType(DB,{type nullable datetime}),

each { _, each if _ = null then null else f_DateToLocal(_)})),

OptionsetColumns = Table.ToList(Table.RemoveColumns(Table.NestedJoin(Table.FromList(Table.ColumnNames(DateToLocal),null,{"ColumnName"}), {"ColumnName"}, #"Optionsets (list)", {"OptionSetName"}, "Optionsets (list)", JoinKind.Inner), {"Optionsets (list)"})),

OptionsetLabels = Table.TransformColumns(DateToLocal,List.Transform(OptionsetColumns,

each{_, each OptionLookup(_, "bookableresource", _) , type text}))

OptionsetLabels

DateToLocal is doing more or less the same: replace the existing value with a new value based on the FunctionOutcome, but here I don't need the columnName to be passes to the function.

The list OptionSetColumns is:

These columns have integers as values:

So for each of these records the step: OptionsetLabels should replace the integer with a text label.

Table.TransformColumns(DateToLocal,List.Transform(OptionsetColumns,

each{_, each OptionLookup(690970000, "bookableresource", "msdyn_startlocation") , type text})) works well for this but with the syntax:

Table.TransformColumns(DateToLocal,List.Transform(OptionsetColumns,

each{_, each OptionLookup(_, "bookableresource", _) , type text})) it does

Table.TransformColumns(DateToLocal,List.Transform(OptionsetColumns,

each{_, each OptionLookup(690970000, "bookableresource", 690970000) , type text}))

When I set OptionsetColumns again it passes the list again, so I should get the _ of the first each again I assume.

I hope this makes sense.

Thanks for the help already.

Re: List.Transform list of columns with return value with function which needs the actual columnName

miguel — Thu, 10 Aug 2023 16:21:32 GMT

It's a bit convoluted for me to provide any actionable suggestions as I'm not entirely sure how the data looks like or if perhaps the data being accessed is correct or not. If you could make it more generic, with some simple lists created with pure M code at a more fundamental level, that would help us tackle this a bit better.

However, if what you're trying to find out is more in line with how you can access a nested value in a way beyond the "each _" or how the "each _" works then I highly recommend the video below. I recorded this a few years back, but its still true to this day:

Adding Columns to Nested Table for Data Consolidation - YouTube