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 ...
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"
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
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"Vijay_A_Verma, worked out for me! Thanks a lot:)