Forum Discussion
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.
Thanks for the suggestion! I used your suggestion to unpivot the columns to align them in 2 columns only. I also took the extra steps of creating additional columns per account category and aggregate the table so place all of the account listings on to one line.
| Cap WO DCC | Cap WO DCC2 | Cap WO MCC | Cap WO MCC2 | Exp WO | Exp WO Secondary | Exp WO DCC | Exp WO Cap MCC | Cap\Exp Acct |
| Cap WO DCC 1001 | Cap WO MCC 1003 | Exp WO 2001 | Cap WO DCC 1001 Cap WO MCC 1003 Exp WO 2001 |
Cap\Exp Acct = [Cap WO DCC]&" "&[Cap WO DCC2]&" "&[Cap WO MCC]&" "&[Cap WO MCC2]&" "&[Exp WO]&" "&[Exp WO Secondary]&" "&[Exp WO DCC]&" "&[Exp WO Cap MCC]
The end result is:
Cap WO DCC 1001 Cap WO MCC 1003 Exp WO 2001
I've been asked to add a carriage break to seperate each account listing, so I added UNICHAR(10) in place of the " " in the code above, however it is creating spaces between the listed accounts. I'm looking for a way to list the accounts and avoid the breaks if one of the column values is BLANK.
Any ideas?
Community Champion@ixdutt Fantastic!! Hmm, line breaks, I recall that the UNICODE route was the way to go but will have to research, any chance you can post an screen shot of what you are seeing?
Thanks for the response. So my data set looks like this:
Project Name Cap WO DCC Cap WO DCC2 Cap WO MCC Cap WO MCC2 Exp WO Exp WO Secondary Exp WO Cap MCC Alpha-Omega Cap WO DCC 3528 Cap WO MCC 375 Cap WO MCC2 1515 Exp WO Secondary 3185 From there I add the code to concatenate the columns:
Captial \ Expense Account = [Cap WO DCC]&UNICHAR(10)&[Cap WO DCC2]&UNICHAR(10)&[Cap WO MCC]&UNICHAR(10)&[Cap WO MCC2]&UNICHAR(10)&[Exp WO]&UNICHAR(10)&[Exp WO Secondary]&UNICHAR(10)&[Exp WO Cap MCC]
My result is the following:
Cap WO DCC 3528
Cap WO MCC 375
Cap WO MCC2 1515
Exp WO Secondary 3185
I am trying to find a way to eliminate the blanks between the carriage breaks.
ixdutt