Power query: Passing a table within a cell to a custom function

Question

I'm pretty new to custom functions in Power Query. I've created this one. Its purpose is to add a custom column whose content AND name are based on the parameters it gets. I've saved it as fnCompactedExamples:

let

    CompactedExamples = (BaseTable as table, ExamplesTable as table, ExampleNumber as text) =>

        let
            Source = BaseTable,
            #"Add example column" = Table.AddColumn( Source, "sb" & ExampleNumber, each Table.Column( ExamplesTable, Campaign & "_b" & ExampleNumber & "_example")),
            #"Extracted values" = Table.TransformColumns(#"Add example column", { "sb" & ExampleNumber, each Text.Combine(List.Distinct(List.Transform(_, Text.From)), "#(lf)"), type text} )
        in
            #"Extracted values"

in
     CompactedExamples

This function gets called in the following query:

let
    Source = #"raw entities table",
    #"Group by cny ID and Cny" = Table.Group(Source, {"Company ID", "Company"}, {{"Data", each _, type table}}),
    #"create sb1 column" = fnCompactedExamples(#"Group by cny ID and Cny", [Data], "1")
in
    #"create sb1 column"

Here is what the table looks like on the "Group by cny ID and Cny" stage

Table

But on the "create sb1 column" stage, I get an error: "Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

I feel close to the end, and that my error is some tiny detail. I scoured the web for hints on my case, but couldn't find any. Can anybody tell me what it that detail?

Edit, per @Olly's request:

The second argument ExamplesTable , is indeed a table that bears columns I want to kinda concatenate without any duplicate: input table

My purpose is to build a table like this, but right now I use a very repetitive series of instructions : output table

Your query calls your function once, and passes a list of tables to the second argument - this is what's causing the error, as the function expects one table for the second argument. It's not clear how to fix this, as your actual requirement isn't clear... Edit your question to show an example of your input and your expected output. — Olly
OK, I added precisions; hope they're clear for you. My edit is awaiting moderation it seems. — mll
I wish you'd post a sample of your actual SOURCE data, rather than a half way point - there's almost certainly a much simpler way of achieving your required output. — Olly

Olly Olly · Accepted Answer · 2018-12-11T08:51:15

Here's a slightly simpler solution, which unpivots your 'example' columns, renames them appropriately, then pivots again, combining distinct values in the output:

let
    Source = Table1,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Company ID", "Company"}, "Attribute", "Value"),
    #"Renamed Attributes" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each "s" & Text.BetweenDelimiters(_, "_", "_"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Attributes", List.Distinct(#"Renamed Attributes"[Attribute]), "Attribute", "Value", each Text.Combine(List.Distinct(_),", "))
in
    #"Pivoted Column"

You can then tweak this to suit - for example, this removes blank values, sorts each list, and separates values with a linefeed:

let
    Source = Table1,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Company ID", "Company"}, "Attribute", "Value"),
    #"Removed Empty Values" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
    #"Renamed Attributes" = Table.TransformColumns(#"Removed Empty Values", {{"Attribute", each "s" & Text.BetweenDelimiters(_, "_", "_"), type text}}),
    #"Sorted Attributes" = Table.Sort(#"Renamed Attributes",{{"Attribute", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Attributes", List.Distinct(#"Sorted Attributes"[Attribute]), "Attribute", "Value", each Text.Combine(List.Sort(List.Distinct(_)),"#(lf)"))
in
    #"Pivoted Column"

Power query: Passing a table within a cell to a custom function

1 Answers