Forum Discussion

Nurzh's avatar
Nurzh
Frequent Visitor
3 years ago

New column based on occurrence of string from another column

Hi all, 

I would like to create a new column in a table that will show me Master Document  numbers for Project Copies. 

Example: 

Table "Document"

 

Document NumberDocument Category
61-2300-P-5090Master Document 
61-2300-P-5090-07812Project Working Copy 
61-2300-P-5090-05908PProject Working Copy 
61-2300-P-5090-05908T1Project Working Copy 
61-2300-P-5090-05908TProject Working Copy 
61-2300-P-5090-07812-21Project Working Copy 
61-2300-P-6090Master Document 
61-2300-P-6090-07812Project Working Copy 
61-2300-P-6090-07812Project Working Copy 

 

Desired New Column that will identify us the Number of Master Document of this copies: 

Document NumberDocument CategoryNew column (Master Documents)
61-2300-P-5090Master Document 61-2300-P-5090
61-2300-P-5090-07812Project Working Copy 61-2300-P-5090
61-2300-P-5090-05908PProject Working Copy 61-2300-P-5090
61-2300-P-5090-05908T1Project Working Copy 61-2300-P-5090
61-2300-P-5090-05908TProject Working Copy 61-2300-P-5090
61-2300-P-5090-07812-21Project Working Copy 61-2300-P-5090
61-2300-P-6090Master Document 61-2300-P-6090
61-2300-P-6090-07812Project Working Copy 61-2300-P-6090
61-2300-P-6090-07812Project Working Copy 61-2300-P-6090

 

The lenght of Master Documents can be different and we need to check it by Category. 

 

One more thing is that the I published this post to DAX section, and get the solution by this formula: 

 

 

 

Master Documents = 
MAXX ( 
    FILTER ( 
        'Table',
        'Table'[Document Category] = "Master Document"
            && CONTAINSSTRING ( EARLIER ( 'Table'[Document Number] ), 'Table'[Document Number] )
    ),
    'Table'[Document Number]
)

 

 

But in this table I have 2.8 million rows, 2.7 million Master Documents of it, and that's why now almost 8 hours I am waiting for the result, it just stuck on "Working on it"... 

 

I am searching if there is any other  ways to realise it in Power Query faster?

 

Thank you for any help! 

Need Help
Tips and Tricks

5 Replies

  • Einomi's avatar
    Einomi
    Icon for Helper V rankHelper V
    3 years ago

    Hi,

     

    Did you try to extract the 14 first characters of the column Document Number, maybe it can be faster

     

    You go to the ribbon

    Add column > Extract > First characters and then type in 14

    • Nurzh's avatar
      Nurzh
      Frequent Visitor
      3 years ago

      Yes I tried, but this will not work for all cases, because the lenght of Master Documents can be different and we need to check it by Category. 

  • slorin's avatar
    slorin
    Icon for Super User rankSuper User
    3 years ago

    Hi

    With Table.Group

    = Table.Group(
    YourSource,
    {"Document Category", "Document Number"},
    {{"Data", each _}},
    GroupKind.Local,
    (x,y) => if y[Document Category]="Master Document" then 1 else 0)

    Then expand columns

     

    Stéphane

    • Nurzh's avatar
      Nurzh
      Frequent Visitor
      3 years ago

      Hi slorin , 

      Thank you, could you please clarify it will be by adding conditional column or need to just group existing column? I mean could you please describe actions step by step?

  • slorin's avatar
    slorin
    Icon for Super User rankSuper User
    3 years ago

    Just group existing column, 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjPUNTI2MNAN0DU1sDRQ0lHyTSwuSS1ScMlPLs1NzStRitVBV6RrYG5haARUGlCUn5WaXKIQnl+UnZmXruCcX1CpgFWDqaWBRQDJOkIMSddCkg6QP3SNiLXFjJgAMiM1gIjREAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document Number" = _t, #"Document Category" = _t]),
    Group = Table.Group(Source,
    {"Document Category", "Document Number"},
    {{"Data", each _}},
    GroupKind.Local,
    (x,y) => if y[Document Category]="Master Document" then 1 else 0),
    RenameColumns = Table.RenameColumns(Group,{{"Document Number", "Master Documents"}}),
    RemoveColumns = Table.RemoveColumns(RenameColumns,{"Document Category"}),
    Expand_Data = Table.ExpandTableColumn(RemoveColumns, "Data", {"Document Number", "Document Category"}, {"Document Number", "Document Category"})
    in
    Expand_Data

     

    Stéphane