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.
Community ChampionGreg_Deckler - are you aware of a way to do this in DAX?
Community ChampionWell edhans - To the best of my knowledge you can't get the column names "dynamically" in DAX, you have to already know what the column names are. I suppose if you did not promote headers and used an Index you could identify names of the columns becaues then they would be rows with an Index of 1. But, it is an interesting thought and I will ponder it.
The other thing Anonymous that I ponder is that I can't fathom how that format of data could possibly be useful in an analysis scenario. Could you enlighten?
- edhans
That is what I was thinking Greg_Deckler - but the promoting of headers happens in Power Query, and if you are there already, might as well implement my code above and do it all there. 😁
- Greg_Deckler
edhans - Hard to argue with that logic! I'm still trying to puzzle through if there are any DAX functions that can return column names in a table without alread knowing them but I can't think of any. But, they keep adding new DAX functions all the time so it is tough to keep track.
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
- edhans
Did my transformations above help Anonymous - there is no way to do that in DAX that I or Greg_Deckler are aware of. DAX is not "self aware" of the column data is in. You have to reference the column(s) specifically. It isn't like Excel where you can use the CELL() and ADDRESS() functions for a formula to figure out where it is in the spreadsheet.
- Greg_Deckler
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.
Hello Greg,
Thanks for the suggestion of unpivoting the columns, it worked like a charm!
ixdutt