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"

BA_Pete

Super User

4 years ago

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

Not applicable

4 years ago

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

Super User

4 years ago

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

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"

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