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.
Morning Greg,
Thanks for your response. To answer your question, my report is designed to help users look up expense accounts related to various projects for the purposes of billing. My data sets are being imported from MS Projects, however when I create the matrix visualization it appears in my first example. I've added filters so the user can narrow down the information displayed down to the project(s) they are looking up, however it appears as multiple columns.
My manager has asked for the expense account listings to appear as a single column which prompted me to place this query. I can only assume that it would be more visually appealing if this report is printed out for upper management.
ixdutt
Community ChampionAnonymous - 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.
Hello Greg,
Thanks for the suggestion of unpivoting the columns, it worked like a charm!
ixdutt
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?
- Greg_Deckler
@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