Create a formula working on any column starting with ...

Hi markgsmith01 ,

Will you get a time column to determine the order of the comments? Create column automaticlly is based on your underlying data. If you can get data with format as below, I think you can achieve your goal.

Sort Date column and group by all rows. Then add an index by group.

For reference: Create Row Number for Each Group in Power BI using Power Query

After some transforms, result is as below. Every time you refresh your report, Power BI will expand the new comment to new columns automaticlly.

Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY7BCsIwDEB/JfQ8WBo9eenBy/YNs4ewBlcoq9gO9e+tPQ3LIBAI7z0yTUqrThES9XjuNYKmC2KZch3B+cT3p4iy3R+o96C8ZwlB1gzeCbcw7eEhvsBF+MTtt/Lik6kGHeaHEo+VOR0yV15hhCRSigKOM5vWaP6Yq7VIeJhbVtZ+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Topic = _t, Date = _t, Comment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Topic", Int64.Type}, {"Date", type datetime}, {"Comment", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Topic"}, {{"Rows", each _, type table [Topic=nullable number, Date=nullable datetime, Comment=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Index",0)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Comment", "Index"}, {"Custom.Comment", "Custom.Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",0,null,Replacer.ReplaceValue,{"Custom.Index"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value", "Custom", each "Comment"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Topic", "Custom.Comment", "Custom", "Custom.Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Reordered Columns", {{"Custom.Index", type text}}, "en-US"),{"Custom", "Custom.Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Custom.Comment")
in
    #"Pivoted Column"

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous,

I'm actually looking to go the other way from what you're outlining here but your comment gave me the tips I needed. I transposed the table, promoted the headers and then unpivoted the columns. This is a better solution for me than concatenating the comments into one column.

let
    Source = Table,
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Thanks Rico!