通过组件前缀过滤JIRA导出CSV

发布于 2025-01-27 22:50:13 字数 699 浏览 2 评论 0原文

我正在努力将JIRA导出CSV的数据馈送到较大的仪表板中。为了使这些数据有用，我需要能够将默认的“组件/s”标头重组为组件特定的标头。

例如，我有以下前缀组件“颜色-BLUE”，“颜色 - 红色”，“ Shape -Square”，“ Shape -Circle”。

JIRA在字母内将现有的“组件/S”列导出。因此，如果Ticket Sample3没有颜色组件，我可能会有以下CSV：

Ticket,Component/s,Component/s
Sample1,Color - Blue,Shape - Square
Sample2,Color - Red, Shape - Circle
Sample3,Shape - Square,

如您所见，通过第一个“组件/S”列过滤不仅包含颜色数据，还包括形状。同样，通过第二个“组件/s”列过滤会完全错过Sample3的形状数据。

我想做的是基于前缀转换此原始数据以创建具有这种格式的新表：

Ticket,Color,Shape
Sample1,Color - Blue,Shape - Square
Sample2,Color - Red, Shape - Circle
Sample3,,Shape - Square

有没有一种方法可以基于前缀字符串在Excel中创建新列？如果影响方法，我的实际数据设置最多包含10列的“组件/s”列。

谢谢！

原文

I'm working on feeding data from a Jira export CSV into a larger dashboard. In order to make this data useful, I need to be able to reorganize the default "Component/s" headers into Component specific headers.

For example, I have the following prefixed components "Color - Blue", "Color - Red", "Shape - Square", "Shape - Circle".

Jira exports existing "Component/s" columns for a given ticket (row) alphabetically. As such, I might have the following CSV if ticket Sample3 didn't have a Color component:

Ticket,Component/s,Component/s
Sample1,Color - Blue,Shape - Square
Sample2,Color - Red, Shape - Circle
Sample3,Shape - Square,

As you can see, filtering by the first "Component/s" column wouldn't only include Color data, it also includes a Shape. Similarly, filtering by the second "Component/s" column would miss Sample3's Shape data altogether.

What I would like to do is transform this raw data based on prefix to create a new sheet with this format:

Ticket,Color,Shape
Sample1,Color - Blue,Shape - Square
Sample2,Color - Red, Shape - Circle
Sample3,,Shape - Square

Is there a way to create new columns in Excel based on a prefix string? If it affects the approach, my actual data set includes up to 10 columns of "Component/s" columns.

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

合久必婚 2025-02-03 22:50:13

continue

Use Power Query with script:

let
    Source = Csv.Document(File.Contents("C:\ ...your path... \jira output.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1.1"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Value", Text.Trim, type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Value", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Value.1", type text}, {"Value.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.1", "Ticket"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Value.1]), "Value.1", "Value.2")
in
    #"Pivoted Column"

where you need to replace C:\ ...your path... \jira output.csv with your actual path and filename.

For example, if given this as the CSV input: