Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

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 ...

4 years ago

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"

Vijay_A_Verma

4 years ago

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

Not applicable

4 years ago

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

Most Valuable Professional

4 years ago

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

Not applicable

4 years ago

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

Most Valuable Professional

4 years ago

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
Not applicable
4 years ago
Vijay_A_Verma, worked out for me! Thanks a lot:)

Forum Discussion

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

Related content

Starts with containing wildcard

User Group Leader Get Started Guide

Measure to SUM if column contains specific text or another column contains specific text

Contain Word

Specific matrix