Combine all of the values a column into one row in another table

[mwllms]

We have a SharePoint list form that has 60+ comment fields. I would like to see it if it is possible in Power BI to combine all of the values from each column into one field in one row in another table?

For example: Each column is a texbox that collects the responses from various company each month. Some fields will be empty because there aren't any changes that month which is okay. 

To make it easier to read, they would like a table that present this information the following way, where the columns would be listed under a column named Questions and the Responses for each company would be combined into one row in the following format: Company Name Comment: 

I tried looking into using CONCATENATEX but I could not figure out how to get it to produce the result above. Any information or suggestions on how to get this accomplished would be greatly appreciated. 

[mwllms]

Thank you all for your help I was able to get it figured out! @ChrisMendoza suggestions worked.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[ChrisMendoza · Accepted solution]

@mwllms -

Maybe some version of:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1QwVNJR8stXSM5IzEtPLQZyUFCsDkKlEVDAEKoQnzpjiInOhE00QZdEkzcFChjjNCgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Company = _t, #"Q1 Comments" = _t, #"Q2 Comments" = _t, #"Q3 Comments" = _t, #"Q4 Comments" = _t, #"Q5 Comments" = _t, #"Q6 Comments" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Q1 Comments", type text}, {"Q2 Comments", type text}, {"Q3 Comments", type text}, {"Q4 Comments", type text}, {"Q5 Comments", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Other Columns", {"Company"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Value] <> "")),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Company", "Value"},Combiner.CombineTextByDelimiter(" : ", QuoteStyle.None),"Comments"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Attribute"}, {{"Comments", each Text.Combine([Comments], ", "), type text}})
in
    #"Grouped Rows"

[Zubair_Muhammad]

Hi @mwllms 

I think you will need to unpivot the comments columns in first table. Then you can link it to second table