Create column with a string that matches a part of another string of different tables

[brumero]

Hi everybody,

I have been reviewing a lot of posts trying to solve the following. I'll do my best to explain it.

The model is based on information about the facilities of the company. This facilities has are related by a hierarchy that has 4 levels

faclvl1 --> faclvl2 --> faclvl3 --> faclvl4

A fact table (Table A) has several records but for all facilities despite their level in the hierarchy. The column is id_fac.

There are 2 major types of facilities, the ones that are included in Table B and Table C and the ones that are only in Table C.

In Table B, I have the four columns with the Hierarchy (faclvl1 / faclvl2 / faclvl3 / faclvl4)

In Table C, I have all facilities in a column and another column specifying the facilities type

So, for the ones that are only in Table C, the only way of creating a relationship is based on the name of the facility that is made like: Child_Parent_ChildCount.

An example of this could be Car-Parkinglot-A-105-001, where Car is the type of facility and the Child, Parkinglot-A-105 is the Parent ID and 001 is the numbering of this child.

So... what I'm trying to do is to extract the Parent of the string to another column to generate the hierarchy as Table B and then merge both tables (the new one and Table B) 

An example of the final table could be the following:

Facility	parking_lot
Car-Parkinglot-A-105-001	Parkinglot-A-105
Parkinglot-A-105	Parkinglot-A-105
Parkinglot-C	Parkinglot-C
WtrDispenser-Parkinglot-B-101-005	Parkinglot-B-101

I have seen several post of @ImkeF  using Text.Contains but I can't make it work.

Thanks to all.

[brumero · Accepted solution]

I have finally manage to solve it, probably, in the most inefficient way.

I duplicate the ArcGIS table and generate a custom column in the original and the new table. The custom column has the value of 1 for each row.

Then I merge both tables, matching by this custom column (creating a massive table). Then I used the Text.Contains function to identify those rows that has inside the Parent string (adding the condition that both fields must not have the same text length).

That's it. 

Thanks for your contribution!

[brumero]

Thank you @FrankAT and  @ImkeF  for attending this topic.

Unfortunately I cannot post the orginal tables.

The problem with @FrankAT suggestion is that there is no unique ID for Parent, there indeed more than 200. The thing is I need to identify those rows that contains the string of another row inside.

Let me expand the example:

Facility	Parent
WtrDispenser-OfficeComp-01-005	OfficeComp-01
Car-Parkinglot-A-105-001	Parkinglot-A-105
Parkinglot-A-105	Parkinglot-A-105
Parkinglot-C	Parkinglot-C
WtrDispenser-Parkinglot-B-101-005	Parkinglot-B-101
SubStation-PwrStation-A-005	PwrStation-A

After all I should be able to have it like this:

faclvl1	faclvl2
WtrDispenser-OfficeComp-01-005	OfficeComp-01
Car-Parkinglot-A-105-001	Parkinglot-A-105
WtrDispenser-Parkinglot-B-101-005	Parkinglot-B-101
SubStation-PwrStation-A-005	PwrStation-A

Thanks

[FrankAT]

Hi @brumero ,

I think the problem lies how you structure your data:

Child	Parent	#	Concatenation with different seperators
Car	Parkinglot-A-105	001	Car,Parkinglot-A-105,001

Thats's a way to identify the parent part.

Regards FrankAT

[brumero]

Thanks @FrankAT ,

This column comes from an ArcGIS table so I cannot re structure the information.

Thanks

[FrankAT]

Hi,

can you give an life example from ArcGis?

Regards FrankAT

[brumero]

Sure, this is a short extract of the table.

I hope you can find this useful.

Many thanks

[brumero]

[brumero]

Hi @FrankAT, any ideas?

Thank you

[FrankAT]

Hi @brumero ,

try the following M-code:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4s0g1ILMrOzEvPyS/RddQ1NDDVNTAwVIrViVZCl0AXdAYLhJcUuWQWF6TmFaeimOUE1GIINAuoLRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Facility = _t]),
    #"Inserted Text Before Delimiter" = Table.AddColumn(Source, "Text Before Delimiter", each Text.BeforeDelimiter([Facility], "Parkinglot"), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Text Before Delimiter", "Custom", each Text.End([Facility],Text.Length([Facility]) - Text.Length([Text Before Delimiter]))),
    #"Inserted Text Before Delimiter1" = Table.AddColumn(#"Added Custom", "Text Before Delimiter.1", each Text.BeforeDelimiter([Custom], "-", 2), type text)
in
    #"Inserted Text Before Delimiter1"

Figure

Regards FrankAT

[ImkeF]

Hi @brumero  

does @FrankAT s solution work for you?

If not, please provide sample data of your input tables as well that match your output table provides. Thanks!