New column based on occurrence of string from another column

[Nurzh]

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 Number	Document Category
61-2300-P-5090	Master Document
61-2300-P-5090-07812	Project Working Copy
61-2300-P-5090-05908P	Project Working Copy
61-2300-P-5090-05908T1	Project Working Copy
61-2300-P-5090-05908T	Project Working Copy
61-2300-P-5090-07812-21	Project Working Copy
61-2300-P-6090	Master Document
61-2300-P-6090-07812	Project Working Copy
61-2300-P-6090-07812	Project Working Copy

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

Document Number	Document Category	New column (Master Documents)
61-2300-P-5090	Master Document	61-2300-P-5090
61-2300-P-5090-07812	Project Working Copy	61-2300-P-5090
61-2300-P-5090-05908P	Project Working Copy	61-2300-P-5090
61-2300-P-5090-05908T1	Project Working Copy	61-2300-P-5090
61-2300-P-5090-05908T	Project Working Copy	61-2300-P-5090
61-2300-P-5090-07812-21	Project Working Copy	61-2300-P-5090
61-2300-P-6090	Master Document	61-2300-P-6090
61-2300-P-6090-07812	Project Working Copy	61-2300-P-6090
61-2300-P-6090-07812	Project 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! 

[slorin]

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 

[slorin]

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]

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?

[Einomi]

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]

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.