Forum Discussion
Adding column that contains data from all columns starting with specific text
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
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"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"
8 Replies
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
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
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
