Adding column that contains data from all columns starting with specific text

[Anonymous]

Hi, 

I am currently converting JIRA data to Excel and load it into Power BI, I ran into some struggle as some columns could be duplicated with a additional number which results into measures not taking everything into account as these columns 

For example in Jira a ticket can be raised and two "watchers" could be assigned. When converting data to Excel it creates two columns called "Watcher", when converting it to PBI it converts the second "Watcher" column to "Watcher_1". 

Problem is that unlimited watchers could be assigned. So it might happen that in the future Watcher_2 till x are created as a column and I need to update my measures again and again.

I am looking for a possibility to add a code in Power Query where it combines all values from columns starting with "Watcher" into a new column. I did find a similar case, but couldnt solve the issue with the code given in that topic.

Code I currently have in Power Query:

let
Source = Excel.Workbook(File.Contents("C:\Users\s.klibi\Samsung SDS\SDSNL OpsEx - General\06 Power BI Playground\Jira PBI\Jira export sample.xlsx"), null, true),
#"KYOCERA Document Solutions 2022_Sheet" = Source{[Item="KYOCERA Document Solutions 2022",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"KYOCERA Document Solutions 2022_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Summary", type text}, {"Issue key", type text}, {"Issue id", Int64.Type}, {"Issue Type", type text}, {"Status", type text}, {"Project key", type text}, {"Project name", type text}, {"Project type", type text}, {"Project lead", type text}, {"Priority", type text}, {"Resolution", type text}, {"Assignee", type text}, {"Reporter", type text}, {"Creator", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Last Viewed", type datetime}, {"Resolved", type datetime}, {"Due Date", type any}, {"Description", type text}, {"Environment", type any}, {"Watchers", type text}, {"Watchers_1", type text}, {"Watchers_2", type text}, {"Watchers_3", type text}, {"Security Level", type text}, {"Custom field (LOH Business Unit)", type text}, {"Custom field (LOH Customer complaint type)", type text}, {"Custom field (LOH Information request)", type text}, {"Custom field (LOH Jira request)", type text}, {"Custom field (LOH Operational request)", type text}, {"Custom field (LOH Product type)", type text}, {"Custom field (Urgency)", type text}, {"Custom field (WEB - Components affected)", type text}, {"Custom field (allowed to view ticket)", type text}, {"Comment", type text}})

in
#"Changed Type"

Code I found in a similar topic:

// Merge only columns that starts with x
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "Watcher")),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Watcher")

In 
#"Merged Columns"

Unfortunately I can't get it done without getting errors, who has the expertise to help me out with this issue. Thanks in advance!

Kind Regards,

Sofiën

[Vijay_A_Verma · Accepted solution]

Your code is allright but a comma is missing at the end of below statement

 #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Proper, type text}})

Hence, following code will work

let
    Source = Excel.Workbook(File.Contents("C:\Users\s.klibi\Samsung SDS\SDSNL OpsEx - General\06 Power BI Playground\Jira PBI\Jira export sample.xlsx"), null, true),
    #"KYOCERA Document Solutions 2022_Sheet" = Source{[Item="KYOCERA Document Solutions 2022",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"KYOCERA Document Solutions 2022_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Summary", type text}, {"Issue key", type text}, {"Issue id", Int64.Type}, {"Issue Type", type text}, {"Status", type text}, {"Project key", type text}, {"Project name", type text}, {"Project type", type text}, {"Project lead", type text}, {"Priority", type text}, {"Resolution", type text}, {"Assignee", type text}, {"Reporter", type text}, {"Creator", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Last Viewed", type datetime}, {"Resolved", type datetime}, {"Due Date", type any}, {"Description", type text}, {"Environment", type any}, {"Watchers", type text}, {"Watchers_1", type text}, {"Watchers_2", type text}, {"Watchers_3", type text}, {"Security Level", type text}, {"Custom field (LOH Business Unit)", type text}, {"Custom field (LOH Customer complaint type)", type text}, {"Custom field (LOH Information request)", type text}, {"Custom field (LOH Jira request)", type text}, {"Custom field (LOH Operational request)", type text}, {"Custom field (LOH Product type)", type text}, {"Custom field (Urgency)", type text}, {"Custom field (WEB - Components affected)", type text}, {"Custom field (allowed to view ticket)", type text}, {"Comment", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Proper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "Watcher")),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Watcher")

In 
	#"Merged Columns"

[BA_Pete]

Hi @Anonymous ,

Personally, I would select all columns that aren't a 'Watcher' column and unpivot other columns.

You can then create a new column based on the new [Attribute] column something like this:

if Text.Contains([Attribute], "Watcher") then "Watcher" else [Attribute]

You can then group on this new column, or just leave it as-is and reference it in your measures etc. to let DAX do the aggregation.

I'm sure there's super-clever ways to achive this with functions and so-on, but sometimes clarity and simplicity win the day.

Pete

[Anonymous]

Hi, I prefer to prevent unpivot columns and continue with the code I found to add a column that collects data from one row where column starts with "Watcher", hope someone could help me out where to add this code. If not, might need to look for another solution like you suggest.

Kind Regards, 

Sofiën

[BA_Pete]

Okay, I'll see if I can help get this solution working for you.

Can you share the full M code with your attempted solution included in it, and provide details of the error you get when you try to implement it please?

Pete

[Anonymous]

My code as is:

let
    Source = Excel.Workbook(File.Contents("C:\Users\s.klibi\Samsung SDS\SDSNL OpsEx - General\06 Power BI Playground\Jira PBI\Jira export sample.xlsx"), null, true),
    #"KYOCERA Document Solutions 2022_Sheet" = Source{[Item="KYOCERA Document Solutions 2022",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"KYOCERA Document Solutions 2022_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Summary", type text}, {"Issue key", type text}, {"Issue id", Int64.Type}, {"Issue Type", type text}, {"Status", type text}, {"Project key", type text}, {"Project name", type text}, {"Project type", type text}, {"Project lead", type text}, {"Priority", type text}, {"Resolution", type text}, {"Assignee", type text}, {"Reporter", type text}, {"Creator", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Last Viewed", type datetime}, {"Resolved", type datetime}, {"Due Date", type any}, {"Description", type text}, {"Environment", type any}, {"Watchers", type text}, {"Watchers_1", type text}, {"Watchers_2", type text}, {"Watchers_3", type text}, {"Security Level", type text}, {"Custom field (LOH Business Unit)", type text}, {"Custom field (LOH Customer complaint type)", type text}, {"Custom field (LOH Information request)", type text}, {"Custom field (LOH Jira request)", type text}, {"Custom field (LOH Operational request)", type text}, {"Custom field (LOH Product type)", type text}, {"Custom field (Urgency)", type text}, {"Custom field (WEB - Components affected)", type text}, {"Custom field (allowed to view ticket)", type text}, {"Comment", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Proper, type text}})
in
    #"Capitalized Each Word"

The code I want to add:

// Merge only columns that starts with x
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "Watcher")),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Watcher")

In 
#"Merged Columns"

How I combine them:

let
    Source = Excel.Workbook(File.Contents("C:\Users\s.klibi\Samsung SDS\SDSNL OpsEx - General\06 Power BI Playground\Jira PBI\Jira export sample.xlsx"), null, true),
    #"KYOCERA Document Solutions 2022_Sheet" = Source{[Item="KYOCERA Document Solutions 2022",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"KYOCERA Document Solutions 2022_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Summary", type text}, {"Issue key", type text}, {"Issue id", Int64.Type}, {"Issue Type", type text}, {"Status", type text}, {"Project key", type text}, {"Project name", type text}, {"Project type", type text}, {"Project lead", type text}, {"Priority", type text}, {"Resolution", type text}, {"Assignee", type text}, {"Reporter", type text}, {"Creator", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Last Viewed", type datetime}, {"Resolved", type datetime}, {"Due Date", type any}, {"Description", type text}, {"Environment", type any}, {"Watchers", type text}, {"Watchers_1", type text}, {"Watchers_2", type text}, {"Watchers_3", type text}, {"Security Level", type text}, {"Custom field (LOH Business Unit)", type text}, {"Custom field (LOH Customer complaint type)", type text}, {"Custom field (LOH Information request)", type text}, {"Custom field (LOH Jira request)", type text}, {"Custom field (LOH Operational request)", type text}, {"Custom field (LOH Product type)", type text}, {"Custom field (Urgency)", type text}, {"Custom field (WEB - Components affected)", type text}, {"Custom field (allowed to view ticket)", type text}, {"Comment", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Proper, type text}})
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "Watcher")),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Watcher")

In 
	#"Merged Columns"

The error I get:

Hope this could give you some insight of what I am trying to achieve. Thanks for the help.

Kind Regards,

Sofiën

[Vijay_A_Verma · Accepted solution]

Your code is allright but a comma is missing at the end of below statement

 #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Proper, type text}})

Hence, following code will work

let
    Source = Excel.Workbook(File.Contents("C:\Users\s.klibi\Samsung SDS\SDSNL OpsEx - General\06 Power BI Playground\Jira PBI\Jira export sample.xlsx"), null, true),
    #"KYOCERA Document Solutions 2022_Sheet" = Source{[Item="KYOCERA Document Solutions 2022",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"KYOCERA Document Solutions 2022_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Summary", type text}, {"Issue key", type text}, {"Issue id", Int64.Type}, {"Issue Type", type text}, {"Status", type text}, {"Project key", type text}, {"Project name", type text}, {"Project type", type text}, {"Project lead", type text}, {"Priority", type text}, {"Resolution", type text}, {"Assignee", type text}, {"Reporter", type text}, {"Creator", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Last Viewed", type datetime}, {"Resolved", type datetime}, {"Due Date", type any}, {"Description", type text}, {"Environment", type any}, {"Watchers", type text}, {"Watchers_1", type text}, {"Watchers_2", type text}, {"Watchers_3", type text}, {"Security Level", type text}, {"Custom field (LOH Business Unit)", type text}, {"Custom field (LOH Customer complaint type)", type text}, {"Custom field (LOH Information request)", type text}, {"Custom field (LOH Jira request)", type text}, {"Custom field (LOH Operational request)", type text}, {"Custom field (LOH Product type)", type text}, {"Custom field (Urgency)", type text}, {"Custom field (WEB - Components affected)", type text}, {"Custom field (allowed to view ticket)", type text}, {"Comment", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Proper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "Watcher")),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Watcher")

In 
	#"Merged Columns"

[Anonymous]

@Vijay_A_Verma Thanks, this worked out for me. Stupid I didnt get this solved myself, was putting the comma after the last line instead. Gets me to a second thing as I am not familiar at all with query language from Power Query. The output I currently get with this second column is:

Is there a code to exclude blanks when creating merged column? I have 10 columns which start with watcher_#. But most of them are blank, so on multiple occasions I get 10 delimiters(;) as output because of a missing watcher.

Kind Regards,

Sofiën

[Vijay_A_Verma · Accepted solution]

Use this

let
    Source = Excel.Workbook(File.Contents("C:\Users\s.klibi\Samsung SDS\SDSNL OpsEx - General\06 Power BI Playground\Jira PBI\Jira export sample.xlsx"), null, true),
    #"KYOCERA Document Solutions 2022_Sheet" = Source{[Item="KYOCERA Document Solutions 2022",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"KYOCERA Document Solutions 2022_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Summary", type text}, {"Issue key", type text}, {"Issue id", Int64.Type}, {"Issue Type", type text}, {"Status", type text}, {"Project key", type text}, {"Project name", type text}, {"Project type", type text}, {"Project lead", type text}, {"Priority", type text}, {"Resolution", type text}, {"Assignee", type text}, {"Reporter", type text}, {"Creator", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Last Viewed", type datetime}, {"Resolved", type datetime}, {"Due Date", type any}, {"Description", type text}, {"Environment", type any}, {"Watchers", type text}, {"Watchers_1", type text}, {"Watchers_2", type text}, {"Watchers_3", type text}, {"Security Level", type text}, {"Custom field (LOH Business Unit)", type text}, {"Custom field (LOH Customer complaint type)", type text}, {"Custom field (LOH Information request)", type text}, {"Custom field (LOH Jira request)", type text}, {"Custom field (LOH Operational request)", type text}, {"Custom field (LOH Product type)", type text}, {"Custom field (Urgency)", type text}, {"Custom field (WEB - Components affected)", type text}, {"Custom field (allowed to view ticket)", type text}, {"Comment", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Status", Text.Proper, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",List.Select(Table.ColumnNames(#"Changed Type"), (x)=> Text.StartsWith(x, "Watcher")),Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Watcher"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Watcher", each Text.Trim(_,";")}})
in
    #"Trimmed Text"

[Anonymous]

@Vijay_A_Verma, worked out for me! Thanks a lot:)