List.Transform list of columns with return value with function which needs the actual columnName
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...
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]),
each{_, each OptionLookup(_, "bookableresource", _) , typetext}))
in
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.
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:
