concatenate column name + cell value

Hi Anonymous ,

The following will merge this data in Power Query. Turns

into this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwNTQwMTVS0lFSgONYHbCEgbmpsSVQwMTYzNTADMYwhysBKTCyAImbm5oZGCK0xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Expense Work Order" = _t, #"Captial Work order DCC" = _t, #"Capital Work Order MCC" = _t, #"Expense WO related to Cap DCC" = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Attribute", "Value"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each not Text.EndsWith([Merged], ", ")),
    #"Grouped Rows" = 
        Table.Group(
            #"Filtered Rows", 
            {"Index"}, 
            {
                {
                    "Test", 
                    each Text.Combine(
                        List.Transform(_[Merged], Text.From), ", "
                    )
                }
            }
        ),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Index"})
in
    #"Removed Columns"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

I don't know how you would do this in DAX. I had to unpivot the columns to get the columns at a field level to work with. I'd be interested to see if someone can post DAX code that can return the field name a value exists in.

Anonymous - Well, you could unpivot your columns. That would put their names in a single column. And then you could get a list of them but no idea what that would do to your data model, etc. In the currently described format, edhans is 100% correct.