Custome col in Power Query

Power Query
1

Hello Team,
I need help to generate the custome col in Power Query.
Problem statement

I am working on JIRA report that has multiple col and few of them are commmet col
each time when end user raise any ticket the new col is created on JIRA ,
Scenario

  1. Project ID-1234 having 10 comment col and comment col I need last(10th col ) comment col.
  2. Project ID-5678 having 11 comment col(10are due to first ID) and value is present in 11th col.
    Final output shoulb be the one custome col which will show the latest non blank value in col.
    I have ahcived in excel but i need to automate this in Power Query

Pleas note number of col will be always increase as new comment added

2

Can you provide a PBIX file please, otherwise we are working in the dark and it will take longer for someone to respond to you.

DJ

3

Okay, allow me sometimes I am sharing

4

Book1.xlsx (8.9 KB)
please here is sample excel

5

Maybe not the most elegant solution but it works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nTRNTQyNlHSUQpPVUgsSlVIz08BcvKzEyuBVFpicmZeuoJncWlpKpALRH75JQqJSTmpCiX5Cjn56Zl5IOFYHbBBpmbmFhBVEOQPNASkLj0fwi/PL8oGGZeWmZeqoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Comment = _t, Comment.1 = _t, Comment.2 = _t, Comment.3 = _t, Comment.4 = _t, Comment.5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Comment", type text}, {"Comment.1", type text}, {"Comment.2", type text}, {"Comment.3", type text}, {"Comment.4", type text}, {"Comment.5", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","","---RemoveBlanks---",Replacer.ReplaceValue,{"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Value] <> "---RemoveBlanks---")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"ID"}, {{"MaxComment", each List.Max([Index]), type number}, {"AllRows", each _, type table [ID=nullable text, Attribute=text, Value=text, Index=number]}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Value", "Index"}, {"Value", "Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllRows", "FilterToLatestComment", each if [MaxComment] = [Index] then "Last Comment" else "Filter this out"),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([FilterToLatestComment] = "Last Comment")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"MaxComment", "Index", "FilterToLatestComment"})
in
    #"Removed Columns"
2 Likes
6

Hi @PankajNamekar,

There are a lot of approaches you can take, I’ve listed three for you to have a go with.
Just copy this into a new blank query and examine each step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(
        Binary.FromText("i45W8nTRNTQyNlHSUQpPVUgsSlVIz08BcvKzEyuBVFpicmZeuoJncWlpKpALRH75JQqJSTmpCiX5Cjn56Zl5IOFYHbBBpmbmFhBVEOQPNASkLj0fwi/PL8oGGZeWmZeqoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Comment = _t, Comment.0 = _t, Comment.1 = _t, Comment.2 = _t, Comment.3 = _t, Comment.4 = _t]),
    Custom0 = Table.AddColumn(Source, "Outcome", each 
        List.Last( List.RemoveMatchingItems( List.Range( Record.ToList(_), 1, Record.FieldCount(_)-1 ), {null, ""} ))
    ),
    Custom1 = Table.AddColumn(Source, "Outcome", each 
        List.Last( List.RemoveMatchingItems( List.Skip( Record.ToList(_), 1 ), {null, ""} ))
    ),
    Custom2 = Table.AddColumn(Source, "Outcome", each 
        List.Last( List.RemoveMatchingItems( Record.ToList( Record.SelectFields( _, List.Select(Record.FieldNames(_), (x)=> Text.StartsWith( x, "Comment", Comparer.OrdinalIgnoreCase )))), {null, ""} ))
    )
in
    Custom2

Step 0. Leverages an offset
Step 1. Just assumes you have one starting column to omit
Step 2. Selects fields based on their name

Here’s the result for each step

image
image1318Ă—75 4.18 KB

Hope this is helpful

2 Likes
7

I won’t bother screenshotting my steps now as this is far more succinct from @Melissa :joy:

1 Like
8

Hey Thanks Melissa
Let me try this one.
But keep remember my comment col number are increasing day by day

9

I will go with both the approach @DavieJoe but up course we cant think like @Melissa :face_with_open_eyes_and_hand_over_mouth:

1 Like
10

@PankajNamekar we can think like @Melissa …it’s just that we have a lot of catching up to do :wink:

1 Like
11

All three solutions can deal with that, but I’m assuming your actual table might look different or include other fields omited here. You can pick one that best suits your real-life data :blush:

@DavieJoe,
I like your approach, there are so many different ways to solve a problem, it’s magic

1 Like
12

@Melissa insted of manually mentioning the comment col 1,2,3 is there any chance where we can generate list of col that contain Comment word in Header and we can tag that entire list in above M-code

13

@Melissa insted of manually mentioning the comment col 1,2,3 is there any chance where we can generate list of col that contain Comment word in Header and we can tag that entire list in above M-codeBlockquote

That would be step => Custom2
@PankajNamekar Please note that each step is a separate approach you can take…

2 Likes
14

Thanks @Melissa I’ve been focussing on other stuff for a while and, I’ll admit, my Power Query/M-Code has suffered. Plan to put some allocated time in each week to get it moving in the right direction again.

1 Like
15

Not able to implement as base file when i import to Power query the multiple steps are auto generated.
I am still trying.

16

When you click on the gear wheel beside the step name in the Applied steps pane you want to use (for example: Custom2), the Add Custom column dialog box opens. You can copy the code from there an transfer it to your file/query without issue if the structure resembles the supplied sample.

If you need more assistance, describe the issue more clearly, include images, errors and so on. Thank you

1 Like
17

[quote=“Melissa, post:6, topic:42276”]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(
        Binary.FromText("i45W8nTRNTQyNlHSUQpPVUgsSlVIz08BcvKzEyuBVFpicmZeuoJncWlpKpALRH75JQqJSTmpCiX5Cjn56Zl5IOFYHbBBpmbmFhBVEOQPNASkLj0fwi/PL8oGGZeWmZeqoBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Comment = _t, Comment.0 = _t, Comment.1 = _t, Comment.2 = _t, Comment.3 = _t, Comment.4 = _t]),
    Custom0 = Table.AddColumn(Source, "Outcome", each 
        List.Last( List.RemoveMatchingItems( List.Range( Record.ToList(_), 1, Record.FieldCount(_)-1 ), {null, ""} ))
    ),
    Custom1 = Table.AddColumn(Source, "Outcome", each 
        List.Last( List.RemoveMatchingItems( List.Skip( Record.ToList(_), 1 ), {null, ""} ))
    ),
    Custom2 = Table.AddColumn(Source, "Outcome", each 
        List.Last( List.RemoveMatchingItems( Record.ToList( Record.SelectFields( _, List.Select(Record.FieldNames(_), (x)=> Text.StartsWith( x, "Comment", Comparer.OrdinalIgnoreCase )))), {null, ""} ))
    )
in
    Custom2
18

Sure Melissa

19

@Melissa
let

20

Hi @PankajNamekar,

On the excel sheet, make sure you don’t exceed 3.000 cells in your selection, press CTRL+C to copy. Go to the Power Query Editor, click on the Enter Data icon and press CTRL+V to paste your values in…

1 Like