Power Query : Column as parameter

[costic]

Hello,

There are some tips here that should have help me but i don't get how to use them, so i post my message :

1) i have a table of opportunities.

2) in this table there are columns with lists of people

3) I extract the names in the list of people of a column to make a new one with the names (not a list)

=> I'd like to make a function with a column name as parameter to extract the names of any columns which contains lists of people

for the moment, the following code is workin, but  :

let

    getPeople = (OpportunitesID) =>

    let

    //get all the list of opportunities in my site

        MyLists = let

        Source = SharePoint.Tables("https://mydomain.sharepoint.com/sites/mysite/", [Implementation="2.0", ViewMode="Default"]),

        opportunitesTable = Source{[Id="123456"]}[Items]

    in

        Source,

        opportunitesTable = MyLists{[Id="123456"]}[Items],

        //get the list of people in the column "People" for each rows

        getListOfPeople = opportunitesTable{[ID=OpportunitiesID]}[People], // I d like "People" being a parameter

        ListOfPeopleToTable = Table.FromList(getListOfPeople, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

        ExpandedCol = Table.ExpandRecordColumn(ListOfPeopleToTable, "Column1", {"lookupValue"}, {"Column1.lookupValue"}),

        Transform.ToQueryString = (table) => Text.RemoveRange(List.Accumulate(Table.ToList(ExpandedCol), "", (a, b) => a & ";" & Text.Replace(b, ",", "=")), 0, 1)

    in

        Transform.ToQueryString(ExpandedCol)

in

getPeople

____

i'd like to manage the case that there is no column/record. I tried to add a record "lookupValue" in the list (transformed into table), but i m not sure to do it well either...

thank you for help

[costic · Accepted solution]

Hey, i ll do that by sequences... for custom function for the moment.

[mahoneypat]

Have you tried using the Table.Column function, which allows you to add the desired column as a text value/parameter?

Pat

[costic]

I'm trying that, but for the moment, it does not work.

[v-kelly-msft]

Hi  @costic ,

Check the similar thread below:

https://stackoverflow.com/questions/44298772/power-query-column-name-as-parameter

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!