Forum Discussion
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 ZhouIf 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!
Hi markgsmith01 ,
Do you have a table with all comments you need? As I understand, I think you want to get Comments based on the string start like "I disagree"/"excellent idea"... and so on. If Comment3 is start by "I disagree" you will add a column and show the strings after "I disagree". I am confused Comment1, it seems that the comment start by "I disagree" and "Can I see the data".
Could you share me a sample file and show me the result you want? This will make it easier for me to find the solution.
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.
The table in the original post is an example of the data I'm getting (as .csv). Think of it like this forum thread. The "Topic" would be the title of the thread and the responses to the forum would appear in these Comment Fields.
You could think of the "Comment" fields as,
Comment = 1st comment
Comment1 = 2nd comment
Comment2 = 3rd comment
When the first topic is created it would have 2 fields, "Topic" and "Comment".
When the first comment is added, the comment would populate into the "Comment" field.
If a second comment is added, a new field, "Comment1" is automatically created, and the second comment populated into that field.
If there are no comments on a topic, then the fields would be blank for that topic
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 ZhouIf 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!