Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi all,
I would like to find the occurence of string in one column, and remove the remain part. Example:
Table "Document"
DocNumber | DocCategory |
61-2300-B-PID-2350-01 | Master |
61-2300-B-PID-2350-01-07812 | Copy |
61-2300-B-PID-2350-01-07812-21 | Copy |
Desired result will be:
DocNumber | DocCategory | New Column |
61-2300-B-PID-2350-01 | Master | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07812 | Copy | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07812-21 | Copy | 61-2300-B-PID-2350-01 |
I have tried this formula, but it does not work correctly.
document master =
VAR CurrentCategory = Document[DocCategory]
VAR CurrentNumber = Document[DocNumber]
VAR IsMasterDocument = CurrentCategory = "Master"
VAR MasterNumber =
IF(
IsMasterDocument,
CurrentNumber,
LEFT(CurrentNumber, IFERROR(FIND("-", CurrentNumber & "-", FIND("-", CurrentNumber) + 1) - 1,LEN(CurrentNumber)))
)
RETURN
MasterNumber
One more thing is the lenght of Master Documents can be different and we need to check it by Category.
Solved! Go to Solution.
Hi @Nurzh
Please refer to attached sample file with the solution
Master Documents =
MAXX (
FILTER (
'Table',
'Table'[Document Category] = "Master Document"
&& CONTAINSSTRING ( EARLIER ( 'Table'[Document Number] ), 'Table'[Document Number] )
),
'Table'[Document Number]
)
Hi @tamerj1 ,
Thank you for your solution,
it really works, but I have 2.8 million rows in my table, and now almost 6-7 hours I am waiting for the result, it just stuck on "Working on it"...
Is there any other ways to realise it, maybe in Power query?
hi @Nurzh
do you have other columns?
if that is all you have, you can just try:
column =
MAXX(
FILTER(data, data[DocCategory]="Master"),
data[DocNumber]
)
This is not suitable for my case, I need to check occurence of every string Master Document in rows where Category = Copy
Hi, @FreemanZ
Yes I have, here is the full table with another example:
Desired new column I make it Bold.
The thing is that we need to identify Master Document value from Copy Document Numbers.
DocumentNumber | DocumentTitle | DocumentTypeShortCode | DocumentType | DocumentSourceCategory | RevisionCode | FinalStatus | NEW COLUMN |
61-2300-B-PID-2350-01 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Master Document | Z08 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07812 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | V01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07917D | L-2350 TENGIZ FLS PIG RCVR (DESTRUCT) | PID | Piping and Instrumentation Diagram | Project Working Copy | Z01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07917 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | Z01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-14398 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | Z03 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-00209 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | V01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07812-21 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | V01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-05209D | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | Z03 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07812-21D1 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | Z01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-05908 | PIPING AND INSTRUMENTATION DIAGRAM L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | V01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07812D | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | V01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-05209 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | Z03 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-03210D | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | Z01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07812-21P | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | Z01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-07812-21D | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | V01 | Z | 61-2300-B-PID-2350-01 |
61-2300-B-PID-2350-01-03210 | L-2350 TENGIZ FLOWLINES PIG RECEIVER | PID | Piping and Instrumentation Diagram | Project Working Copy | Z01 | Z | 61-2300-B-PID-2350-01 |
hi @Nurzh
not sure if your case is complete. try like:
Column =
MAXX(
FILTER(
data,
data[DocumentSourceCategory]="Master Document"
),
data[DocumentNumber]
)
it worked like:
Tried this formula, it does not suitable for my case, as I have many various document copies. This takes only max value of all master documents.
The thing is we need to search for occurence of Document Number where Category = Master in Document Numbers where Category = Copy
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.