Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
markgsmith01
Frequent Visitor

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

I'm recieving data that populates each comment in a new column. The comments should really be in a separate table that is related to the "Topic". In the example below, if a new Comment was added to Topic 1, then a new Column3 would be created.

 

I feel I have a couple of options (although am not sure how to do either).

 

  1. split the comments into a separate table
  2. concatenate the comments into one field

I can do this if I know what the columns are but can I set up a formula to work on any columns that start with "Comment" to allow for if new Comment columns are created?

 

TopicCommentComment1Comment2
1I disagreeexcellent ideaHow do you do this?
2

Hello

  
3

Can I see the data?

How can I help? 
2 ACCEPTED SOLUTIONS

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.

RicoZhou_0-1651654371130.png

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.

RicoZhou_1-1651654709993.png

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.

 

View solution in original post

Hi @v-rzhou-msft,

 

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!

View solution in original post

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1651654371130.png

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.

RicoZhou_1-1651654709993.png

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 @v-rzhou-msft,

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.