How can i find exact word from table 1 in other table

[lboldrino]

Hi.

my tblCluster:

searchword	Cluster
Elda	ELDA
elda	ELDA
ELDA	ELDA
ÖGK	ELDA
mbgm	Mbg
Stepp Berech	Stepp Berechtigung
Stepp Account	Stepp Berechtigung
Stepp Kennword	Stepp Berechtigung

my tblTitel

col

Elda-Ermittlungen

ELDA

Elda vs ÖGK

WG: ELDA Zugang für Spar

SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020

Fehlermeldungen in ELDA Protokoll

Fwd: Transport, ELDA-Arbeitsstättenmeldung

elda postfächer

mbgm wurde nicht an die ÖGK übermittelt

Umleitung der ÖGK Protokolle auf anderen Drucker

Berechtigungen für WEBEKU, ÖGK - für neue rote Systempartner

bla bla bla Stepp Kennwort

bla bla Stepp Account XXXXX

 i need to add a new Cluster_Cloum in tblTitel with Cluster frpm tblCluster:

col	Cluster
Elda-Ermittlungen	ELDA
ELDA	ELDA
Elda vs ÖGK	ELDA
WG: ELDA Zugang für Spar	ELDA
SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020
Fehlermeldungen in ELDA Protokoll	ELDA
Fwd: Transport, ELDA-Arbeitsstättenmeldung	ELDA
elda postfächer	ELDA
mbgm wurde nicht an die ÖGK übermittelt	Mbg,ELDA
Umleitung der ÖGK Protokolle auf anderen Drucker	ELDA
Berechtigungen für WEBEKU, ÖGK - für neue rote Systempartner	ELDA
bla bla bla Stepp Kennwort	Stepp Berechtigung
bla bla Stepp Account XXXXX	Stepp Berechtigung

i try with Customer Col: 

= Table.AddColumn(#"Changed Type", "Custom", each let myvalue=[col]
in
Text.Combine(
Table.SelectRows(tblCluster,
each Text.Contains(myvalue,[Schlagwort]))[Clustername]
,",")) 

but the Result is False:

titel	Cluster
Elda-Ermittlungen	ELDA
ELDA	ELDA
Elda vs ÖGK	ELDA, ELDA
WG: ELDA Zugang für Spar	ELDA
SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020	ELDA
Fehlermeldungen in ELDA Protokoll	ELDA
Fwd: Transport, ELDA-Arbeitsstättenmeldung	ELDA
elda postfächer	ELDA
mbgm wurde nicht an die ÖGK übermittelt	Mbg,ELDA
Umleitung der ÖGK Protokolle auf anderen Drucker	ELDA
Berechtigungen für WEBEKU, ÖGK - für neue rote Systempartner	ELDA
bla bla bla Stepp Kennwort	Stepp Berechtigung
bla bla Stepp Account XXXXX	Stepp Berechtigung

do you have any Idea ?

best Greeting 🙂

[Jimmy801 · Accepted solution]

Hello @lboldrino 

then try this

let
    tblCluster = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1JSVTSUXL1cXFUitWJVkpF44MZSPzD09y9kfm5Sem5QL5vUjqYG1ySWlCg4JRalJqcARRG5pZkppfmIatyTE7OL80rIaTMOzUvrzy/KAWHulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [searchword = _t, Cluster = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"searchword", type text}, {"Cluster", type text}})
    in
        #"Changed Type",
    
    tblTitle = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBNbsIwEIWvMmJNUMuu2QWRsqALpBRRgVg4yRCs+Ceyx0W9SE/AMdjlYh1sWqmRLEXvvfnm53CYlKoVWem0JFLBdGgmxymrb8si/bANnx7G79U6CrtVDncX9qETpoPTeHNQDcJFtyo2sKmKHCoSFDxI01vnsCfIYC+RYRR0DvOX2fNs/jR/ikWveFboNLtxAC5KHTbOku2tUil1aXN4d8L4wTqaxkhWuBoleU/jlQjNgxHz92YwWE+n8dqcMc2n607DJbgWwcjmTCAMtBLjejDeaoyHQEUxvdWK6QyEFl3K/M2EIMKJy9nhkZcuNP2jx4IVRssubRMPtCsX5Xo7TYwsaQYDAuMQqi9PqPmGZB6MWgn4fRXhMMAajbnw4v/sZBVNY4Mh+Lh/k+PxBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"col", type text}})
    in
        #"Changed Type",
    
    Splitter = ". ,-",
    GetSearchWordAsList =List.Transform(tblCluster[searchword], each {{_},Text.SplitAny(_,Splitter)}),
    AddColumnCluster = Table.AddColumn
    (
        tblTitle,
        "Cluster",
        (add)=> 
        let 
            ApplySplitter = Text.SplitAny(add[col],Splitter),
            CheckIfIntersect = List.Select(List.Transform(GetSearchWordAsList, each if List.Count(List.Intersect({_{1}, ApplySplitter}))=List.Count(_{1}) then _{0}{0} else false ),each _ <> false),
            GetCluster = Text.Combine(List.Distinct(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[Cluster]{0})), ", ")
   


        in 
            GetCluster
    )
in
    AddColumnCluster

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @lboldrino 

please go on an error and sent me a screenshot.

Most probably you columnname is not "col" as you posted in your first post

Jimmy

[Jimmy801]

Hello @lboldrino 

try this. The step AddColumnCluster does the trick. It's complex but it's working

let
    tblCluster = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1JSVTSUXL1cXFUitWJVkpF44MZSPzD09y9kfm5Sem5QL5vUjqYG1ySWlCg4JRalJqcARRG5pZkppfmIatyTE7OL80rIaTMOzUvrzy/KAWHulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [searchword = _t, Cluster = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"searchword", type text}, {"Cluster", type text}})
    in
        #"Changed Type",
    
    tblTitle = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBNbsIwEIWvMmJNUMuu2QWRsqALpBRRgVg4yRCs+Ceyx0W9SE/AMdjlYh1sWqmRLEXvvfnm53CYlKoVWem0JFLBdGgmxymrb8si/bANnx7G79U6CrtVDncX9qETpoPTeHNQDcJFtyo2sKmKHCoSFDxI01vnsCfIYC+RYRR0DvOX2fNs/jR/ikWveFboNLtxAC5KHTbOku2tUil1aXN4d8L4wTqaxkhWuBoleU/jlQjNgxHz92YwWE+n8dqcMc2n607DJbgWwcjmTCAMtBLjejDeaoyHQEUxvdWK6QyEFl3K/M2EIMKJy9nhkZcuNP2jx4IVRssubRMPtCsX5Xo7TYwsaQYDAuMQqi9PqPmGZB6MWgn4fRXhMMAajbnw4v/sZBVNY4Mh+Lh/k+PxBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"col", type text}})
    in
        #"Changed Type",
    
    AddColumnCluster = Table.AddColumn
    (
        tblTitle,
        "Cluster",
        (add)=> Text.Combine( List.Distinct(List.Transform(List.Distinct(List.Select(List.Transform(tblCluster[searchword], (trans)=>  if Text.Contains(add[col],trans) then trans else null),each _ <> null)), (trans)=> Table.SelectRows(tblCluster, each [searchword]=trans)[Cluster]{0})),", ")
    )
in
    AddColumnCluster

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lboldrino]

@jimmy:

"SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020" contains the substring "elda".

 any idea?

here is the Result from your Query:

colCluster

Elda-Ermittlungen	ELDA
ELDA	ELDA
Elda vs ÖGK	ELDA
WG: ELDA Zugang für Spar	ELDA
SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020	ELDA
Fehlermeldungen in ELDA Protokoll	ELDA
Fwd: Transport, ELDA-Arbeitsstättenmeldung	ELDA
elda postfächer	ELDA
mbgm wurde nicht an die ÖGK übermittelt	ELDA, Mbg
Umleitung der ÖGK Protokolle auf anderen Drucker	ELDA
Berechtigungen für WEBEKU, ÖGK - für neue rote Systempartner	ELDA
bla bla bla Stepp Kennwort
bla bla Stepp Account XXXXX	Stepp Berechtigung

Big ThanX 🙂

[Jimmy801]

Hello @lboldrino 

i didn't see that you are searching for whole words. Tried to adapt the code. In the step Splitter you have to define the characters that define a whole word. I putted now space, dot, comma and "-". Check it out

let
    tblCluster = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1JSVTSUXL1cXFUitWJVkpF44MZSPzD09y9kfm5Sem5QL5vUjqYG1ySWlCg4JRalJqcARRG5pZkppfmIatyTE7OL80rIaTMOzUvrzy/KAWHulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [searchword = _t, Cluster = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"searchword", type text}, {"Cluster", type text}})
    in
        #"Changed Type",
    
    tblTitle = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBNbsIwEIWvMmJNUMuu2QWRsqALpBRRgVg4yRCs+Ceyx0W9SE/AMdjlYh1sWqmRLEXvvfnm53CYlKoVWem0JFLBdGgmxymrb8si/bANnx7G79U6CrtVDncX9qETpoPTeHNQDcJFtyo2sKmKHCoSFDxI01vnsCfIYC+RYRR0DvOX2fNs/jR/ikWveFboNLtxAC5KHTbOku2tUil1aXN4d8L4wTqaxkhWuBoleU/jlQjNgxHz92YwWE+n8dqcMc2n607DJbgWwcjmTCAMtBLjejDeaoyHQEUxvdWK6QyEFl3K/M2EIMKJy9nhkZcuNP2jx4IVRssubRMPtCsX5Xo7TYwsaQYDAuMQqi9PqPmGZB6MWgn4fRXhMMAajbnw4v/sZBVNY4Mh+Lh/k+PxBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"col", type text}})
    in
        #"Changed Type",
    
    Splitter = ". ,-",
    GetSearchWordAsList =List.Transform(tblCluster[searchword], each {{_},Text.SplitAny(_,Splitter)}),
    AddColumnCluster = Table.AddColumn
    (
        tblTitle,
        "Cluster",
        (add)=> 
        let 
            ApplySplitter = Text.SplitAny(add[col],Splitter),
            CheckIfIntersect = List.Select(List.Transform(GetSearchWordAsList, each if List.Count(List.Intersect({_{1}, ApplySplitter}))=List.Count(_{1}) then _{0}{0} else false ),each _ <> false),
            GetCluster = Text.Combine(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[Cluster]{0}), ", ")
   


        in 
            GetCluster
    )
in
    AddColumnCluster

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lboldrino]

The Result: 

colCluster

Elda-Ermittlungen	ELDA
ELDA	ELDA
Elda vs ÖGK	ELDA, ELDA
WG: ELDA Zugang für Spar	ELDA
SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020
Fehlermeldungen in ELDA Protokoll	ELDA
Fwd: Transport, ELDA-Arbeitsstättenmeldung	ELDA
elda postfächer	ELDA
mbgm wurde nicht an die ÖGK übermittelt	ELDA, Mbg
Umleitung der ÖGK Protokolle auf anderen Drucker	ELDA
Berechtigungen für WEBEKU, ÖGK - für neue rote Systempartner	ELDA
bla bla bla Stepp Kennwort
bla bla Stepp Account XXXXX	Stepp Berechtigung

???

[Jimmy801 · Accepted solution]

Hello @lboldrino 

then try this

let
    tblCluster = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1JSVTSUXL1cXFUitWJVkpF44MZSPzD09y9kfm5Sem5QL5vUjqYG1ySWlCg4JRalJqcARRG5pZkppfmIatyTE7OL80rIaTMOzUvrzy/KAWHulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [searchword = _t, Cluster = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"searchword", type text}, {"Cluster", type text}})
    in
        #"Changed Type",
    
    tblTitle = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBNbsIwEIWvMmJNUMuu2QWRsqALpBRRgVg4yRCs+Ceyx0W9SE/AMdjlYh1sWqmRLEXvvfnm53CYlKoVWem0JFLBdGgmxymrb8si/bANnx7G79U6CrtVDncX9qETpoPTeHNQDcJFtyo2sKmKHCoSFDxI01vnsCfIYC+RYRR0DvOX2fNs/jR/ikWveFboNLtxAC5KHTbOku2tUil1aXN4d8L4wTqaxkhWuBoleU/jlQjNgxHz92YwWE+n8dqcMc2n607DJbgWwcjmTCAMtBLjejDeaoyHQEUxvdWK6QyEFl3K/M2EIMKJy9nhkZcuNP2jx4IVRssubRMPtCsX5Xo7TYwsaQYDAuMQqi9PqPmGZB6MWgn4fRXhMMAajbnw4v/sZBVNY4Mh+Lh/k+PxBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"col", type text}})
    in
        #"Changed Type",
    
    Splitter = ". ,-",
    GetSearchWordAsList =List.Transform(tblCluster[searchword], each {{_},Text.SplitAny(_,Splitter)}),
    AddColumnCluster = Table.AddColumn
    (
        tblTitle,
        "Cluster",
        (add)=> 
        let 
            ApplySplitter = Text.SplitAny(add[col],Splitter),
            CheckIfIntersect = List.Select(List.Transform(GetSearchWordAsList, each if List.Count(List.Intersect({_{1}, ApplySplitter}))=List.Count(_{1}) then _{0}{0} else false ),each _ <> false),
            GetCluster = Text.Combine(List.Distinct(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[Cluster]{0})), ", ")
   


        in 
            GetCluster
    )
in
    AddColumnCluster

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lboldrino]

Dear Jimmy801,

the code works perfect, i will added the searchword, that found to cluster as a new column in my Ticketstables. 

any idee?

thanx!

[Jimmy801]

Hello @lboldrino 

sorry, I can not follow you. Can you make me an example on the solution I provided... what exactly you need to have.

BR

Jimmy

[lboldrino]

Hi.

my tblCluster:

searchword         Cluster
Elda                      ELDA
elda                      ELDA
ELDA                     ELDA
ÖGK                      ELDA
mbgm                   Mbg
Stepp Berech        Stepp Berechtigung
Stepp Account     Stepp Berechtigung
Stepp Kenn          Stepp Berechtigung
my tblTitel

Title
Elda-Ermittlungen
ELDA
Elda vs ÖGK
WG: ELDA Zugang für Spar
SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020
Fehlermeldungen in ELDA Protokoll
Fwd: Transport, ELDA-Arbeitsstättenmeldung
elda postfächer
mbgm wurde nicht an die ÖGK übermittelt
Umleitung der ÖGK Protokolle auf anderen Drucker
Berechtigungen für WEBEKU, ÖGK - für neue rote Systempartner
bla bla bla Stepp Kennwort
bla bla Stepp Account XXXXX

the Result with your code ist:

The Result: 

Title                                                                                                         Cluster                                    Elda-Ermittlungen                                                                                        ELDA
ELDA                                                                                                            ELDA
Elda vs ÖGK                                                                                                  ELDA
WG: ELDA Zugang für Spar ELDA
SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020
Fehlermeldungen in ELDA Protokoll                                                            ELDA
Fwd: Transport, ELDA-Arbeitsstättenmeldung                                             ELDA
elda postfächer                                                                                             ELDA
mbgm wurde nicht an die ÖGK übermittelt                                                  ELDA, Mbg
Umleitung der ÖGK Protokolle auf anderen Drucker                                   ELDA
Berechtigungen für WEBEKU, ÖGK - für neue rote Systempartner                      ELDA
bla bla bla Stepp Kennwort                                                                            Stepp Berechtigung

bla bla Stepp Account XXXXX                                                                              Stepp Berechtigung

now, i need to know, which serachword si found in every row:

Title                                                                                           Cluster                                       Found-Searchword

elda-Ermittlungen                                                                            ELDA                                               elda
ELDA                                                                                                 ELDA                                              ELDA
Elda vs ÖGK                                                                                       ELDA                                              Elda,ÖGK
WG: ELDA Zugang für Spar ELDA
SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020
Fehlermeldungen in ELDA Protokoll                                                     ELDA                                              ELDA
Fwd: Transport, ELDA-Arbeitsstättenmeldung                                      ELDA                                              ELDA
elda postfächer                                                                                     ELDA                                              elda
mbgm wurde nicht an die ÖGK übermittelt                                          ELDA, Mbg                                  mbgm,Ögk
Umleitung der ÖGK Protokolle auf anderen Drucker                            ELDA                                              ÖGK
Berechtigungen für WEBEKU, ÖGK - für neue rote Systempartner       ELDA                                             ÖGK
bla bla bla Stepp Kennwort                                                                    Stepp Berechtigung                        stepp

bla bla  Stepp Account XXXXX                                                                 Stepp Berechtigung                       stepp

[Jimmy801]

Hello @lboldrino 

check out this code if it works

let
    tblCluster = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1JSVTSUXL1cXFUitWJVkpF44MZSPzD09y9kfm5Sem5QL5vUjqYG1ySWlCg4JRalJqcARRG5pZkppfmIatyTE7OL80rIaTMOzUvrzy/KAWHulgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [searchword = _t, Cluster = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"searchword", type text}, {"Cluster", type text}})
    in
        #"Changed Type",
    
    tblTitle = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZBNbsIwEIWvMmJNUMuu2QWRsqALpBRRgVg4yRCs+Ceyx0W9SE/AMdjlYh1sWqmRLEXvvfnm53CYlKoVWem0JFLBdGgmxymrb8si/bANnx7G79U6CrtVDncX9qETpoPTeHNQDcJFtyo2sKmKHCoSFDxI01vnsCfIYC+RYRR0DvOX2fNs/jR/ikWveFboNLtxAC5KHTbOku2tUil1aXN4d8L4wTqaxkhWuBoleU/jlQjNgxHz92YwWE+n8dqcMc2n607DJbgWwcjmTCAMtBLjejDeaoyHQEUxvdWK6QyEFl3K/M2EIMKJy9nhkZcuNP2jx4IVRssubRMPtCsX5Xo7TYwsaQYDAuMQqi9PqPmGZB6MWgn4fRXhMMAajbnw4v/sZBVNY4Mh+Lh/k+PxBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [col = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"col", type text}})
    in
        #"Changed Type",
    
    Splitter = ". ,-",
    GetSearchWordAsList =List.Transform(tblCluster[searchword], each {{_},Text.SplitAny(_,Splitter)}),
    AddColumnCluster = Table.AddColumn
    (
        tblTitle,
        "Cluster",
        (add)=> 
        let 
            ApplySplitter = Text.SplitAny(add[col],Splitter),
            CheckIfIntersect = List.Select(List.Transform(GetSearchWordAsList, each if List.Count(List.Intersect({_{1}, ApplySplitter}))=List.Count(_{1}) then _{0}{0} else false ),each _ <> false),
            GetCluster = Text.Combine(List.Distinct(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[Cluster]{0})), ", "),
            GetSearchWord = Text.Combine(List.Distinct(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[searchword]{0})), ", ")
   


        in 
            [Cluster = GetCluster, Searchword = GetSearchWord]
    ),
    #"Expanded Cluster" = Table.ExpandRecordColumn(AddColumnCluster, "Cluster", {"Cluster", "Searchword"}, {"Cluster", "Searchword"})
in
    #"Expanded Cluster"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lboldrino]

Hier ist my current original code:

let
tblCluster =
let
Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZXfTsIwFMZfpeFaovHC+6ELJrCEMNRE9KIbh9Gsf8hpJ4kP4tNwtxez3RiCm67crOn5ft/y9bTrlstBOH0IBlfN8HgfDd6vloPyazxpV8eTjmIgBfBVuy6STNipGI2jk2rKgSKTWVsp9wmgYMbwQmak262NQqmuG9SRbSh+HkY2UacWyoxmQIYkTjfI1uYDMIcNkqoFtXaunEb/4OwT0IM8CNqU+zQHD8Mx1gswA8jt0zXhrbi5ub3z8I9AUqelG8My6wTpYQoSTf3IiDKuvWLsAJO68f1shuVea0trbSFgSZUlDmakczmN8YVxnishQF7mCwpt0B4vH3ZCEeSnDxkVxm6YDVIYLz5kktPD2eyFZ/bNSlJOc5f6fNYwK7alf4qv9iDZ6WE4rSaFjVyl+CXCggno9gCuabVh3bJbyA6Y7lZpgpBuZKc7hu3WdsIBJ12Iw9msXT3zzJ0GfuwEpNwpNH40TVNVyH44BybhkIMwQZy513QukHW5R7JQhiLttdYYedLVRfQ/O3fXj7ucIVG1MrXfILe3r8aWdDRFIz+wQeAyXJOfWBc68VLjdOi9mmjhwPp53CYlC92q3s+jVi3kK/rrD/j+DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Spalte ""1""" = _t, #"Spalte ""2""" = _t, Service = _t]),
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte ""1""", type text}, {"Spalte ""2""", type text}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"Spalte ""1""", "searchword"}, {"Spalte ""2""", "Cluster"}})
in
#"Umbenannte Spalten",

Tickets =
let
Source = Excel.Workbook(File.Contents("F:\data\AS\BI\Reporting\Power BI\Reports\Spar\DqExcels\MergeTickets.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Incident ID", type text}, {"Open Time", type date}, {"Close Time", type date}, {"Closed Month", type text}, {"Priority Code", Int64.Type}, {"Status", type text}, {"Assignment Group", type text}, {"Time Spend AG", type text}, {"IM Time Spend RecordAG", type text}, {"Time Spend (Sum in Minutes)", Int64.Type}, {"Category", type text}, {"Title", type text}, {"Classification", type text}, {"Service", type text}, {"Accounting Information", type text}, {"Closure", type text}, {"Initiated by", type text}, {"Causecode", type text}, {"Department", type text}, {"Country", type text}}),
#"Added monitoring" = Table.AddColumn(#"Changed Type", "monitoring", each if [Initiated by] = "monitoring@spar-ics.com" then "monitoring" else "user"),
#"Added title kurz" = Table.AddColumn(#"Added monitoring", "title kurz", each Text.Start([Title],25)),
#"Added url" = Table.AddColumn(#"Added title kurz", "url", each "http://sc.int.spar.at/sm9/index.do?ctx=docEngine&file=probsummary&query=number%3D%22" & [Incident ID] & "%22&action=&title=Update%20Incident%20" & [Incident ID] & "&lang=")
in
#"Added url",
#"Filtered Rows" = Table.SelectRows(#"AddColumn Cluster", each [Incident ID] <> null and [Incident ID] <> ""),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Close Time", type datetime}, {"Open Time", type datetime}}),
#"Added ClosedYear" = Table.AddColumn(#"Changed Type", "ClosedYear", each DateTime.ToText([Close Time],"yyyy")),
#"Inserted Date Subtraction" = Table.AddColumn(#"Added ClosedYear", "Subtraction", each Duration.Days([Close Time]- [Open Time] ), Int64.Type),
Splitter = ". ,-",
GetSearchWordAsList =List.Transform(tblCluster[searchword], each {{_},Text.SplitAny(_,Splitter)}),
#"AddColumn Cluster" = Table.AddColumn(Tickets, "Cluster", each (add)=>
let
ApplySplitter = Text.SplitAny(add[col],Splitter),
CheckIfIntersect = List.Select(List.Transform(GetSearchWordAsList, each if List.Count(List.Intersect({_{1}, ApplySplitter}))=List.Count(_{1}) then _{0}{0} else false ),each _ <> false),
GetCluster = Text.Combine(List.Distinct(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[Cluster]{0})), ", "),
GetSearchWord = Text.Combine(List.Distinct(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[searchword]{0})), ", ")

in
[Cluster = GetCluster, Searchword = GetSearchWord])
in
    #"AddColumn Cluster"

The Result is not correct:

no searchword-col, and cluster-col is not correct?

😞

[Jimmy801]

Hello @lboldrino 

you implemented it wrongly. your code should be like this

let
tblCluster =
let
Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZXfTsIwFMZfpeFaovHC+6ELJrCEMNRE9KIbh9Gsf8hpJ4kP4tNwtxez3RiCm67crOn5ft/y9bTrlstBOH0IBlfN8HgfDd6vloPyazxpV8eTjmIgBfBVuy6STNipGI2jk2rKgSKTWVsp9wmgYMbwQmak262NQqmuG9SRbSh+HkY2UacWyoxmQIYkTjfI1uYDMIcNkqoFtXaunEb/4OwT0IM8CNqU+zQHD8Mx1gswA8jt0zXhrbi5ub3z8I9AUqelG8My6wTpYQoSTf3IiDKuvWLsAJO68f1shuVea0trbSFgSZUlDmakczmN8YVxnishQF7mCwpt0B4vH3ZCEeSnDxkVxm6YDVIYLz5kktPD2eyFZ/bNSlJOc5f6fNYwK7alf4qv9iDZ6WE4rSaFjVyl+CXCggno9gCuabVh3bJbyA6Y7lZpgpBuZKc7hu3WdsIBJ12Iw9msXT3zzJ0GfuwEpNwpNH40TVNVyH44BybhkIMwQZy513QukHW5R7JQhiLttdYYedLVRfQ/O3fXj7ucIVG1MrXfILe3r8aWdDRFIz+wQeAyXJOfWBc68VLjdOi9mmjhwPp53CYlC92q3s+jVi3kK/rrD/j+DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Spalte ""1""" = _t, #"Spalte ""2""" = _t, Service = _t]),
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Spalte ""1""", type text}, {"Spalte ""2""", type text}}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Geänderter Typ",{{"Spalte ""1""", "searchword"}, {"Spalte ""2""", "Cluster"}})
in
#"Umbenannte Spalten",

Tickets =
let
Source = Excel.Workbook(File.Contents("F:\data\AS\BI\Reporting\Power BI\Reports\Spar\DqExcels\MergeTickets.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Incident ID", type text}, {"Open Time", type date}, {"Close Time", type date}, {"Closed Month", type text}, {"Priority Code", Int64.Type}, {"Status", type text}, {"Assignment Group", type text}, {"Time Spend AG", type text}, {"IM Time Spend RecordAG", type text}, {"Time Spend (Sum in Minutes)", Int64.Type}, {"Category", type text}, {"Title", type text}, {"Classification", type text}, {"Service", type text}, {"Accounting Information", type text}, {"Closure", type text}, {"Initiated by", type text}, {"Causecode", type text}, {"Department", type text}, {"Country", type text}}),
#"Added monitoring" = Table.AddColumn(#"Changed Type", "monitoring", each if [Initiated by] = "monitoring@spar-ics.com" then "monitoring" else "user"),
#"Added title kurz" = Table.AddColumn(#"Added monitoring", "title kurz", each Text.Start([Title],25)),
#"Added url" = Table.AddColumn(#"Added title kurz", "url", each "http://sc.int.spar.at/sm9/index.do?ctx=docEngine&file=probsummary&query=number%3D%22" & [Incident ID] & "%22&action=&title=Update%20Incident%20" & [Incident ID] & "&lang=")
in
#"Added url",
Splitter = ". ,-",
GetSearchWordAsList =List.Transform(tblCluster[searchword], each {{_},Text.SplitAny(_,Splitter)}),
#"AddColumn Cluster" = Table.AddColumn(Tickets, "Cluster", (add)=>
let
ApplySplitter = Text.SplitAny(add[col],Splitter),
CheckIfIntersect = List.Select(List.Transform(GetSearchWordAsList, each if List.Count(List.Intersect({_{1}, ApplySplitter}))=List.Count(_{1}) then _{0}{0} else false ),each _ <> false),
GetCluster = Text.Combine(List.Distinct(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[Cluster]{0})), ", "),
GetSearchWord = Text.Combine(List.Distinct(List.Transform(CheckIfIntersect, (searchword)=> Table.SelectRows(tblCluster, each [searchword]=searchword)[searchword]{0})), ", ")

in
[Cluster = GetCluster, Searchword = GetSearchWord]),
#"Filtered Rows" = Table.SelectRows(#"AddColumn Cluster", each [Incident ID] <> null and [Incident ID] <> ""),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Close Time", type datetime}, {"Open Time", type datetime}}),
#"Added ClosedYear" = Table.AddColumn(#"Changed Type", "ClosedYear", each DateTime.ToText([Close Time],"yyyy")),
#"Inserted Date Subtraction" = Table.AddColumn(#"Added ClosedYear", "Subtraction", each Duration.Days([Close Time]- [Open Time] ), Int64.Type)
in
#"Inserted Date Subtraction"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[lboldrino]

if i expand the cluster-col:

[Jimmy801 · Accepted solution]

Hello @lboldrino 

please go on an error and sent me a screenshot.

Most probably you columnname is not "col" as you posted in your first post

Jimmy

[lboldrino · Accepted solution]

yessss. col was worng. it works !

now are my thbcluster only for one departement, but in 2021, i have a new column in tblcluster "Departement", 

how can i get departement from tbcluster in my ticket-table??

thanx 🙂

you are the Best! 🙂

[Jimmy801]

Hello @lboldrino 

add another column that is referencing to that column instead of "col"

I would appreciate if you would accept my post that answered your second request as solution

Many thanks

😗

Jimmy

[lboldrino]

Perfect!

You're the Best!😍

[Anonymous]

hi @lboldrino 

you've got to take a look at my answer?

[lboldrino]

@Anonymous 

thanx for your answer.

but i can not change the titels... there are the customer-data from extern customer, and i have to report this...

there are very others titles, with this problem..

😞

[Anonymous]

Hi @lboldrino 

"...

but i can not change the titels... there are the customer-data from extern customer, and i have to report this...

there are very others titles, with this problem..

"

I was not referring to how to solve the problem (even if I had given a reference to a post that solved a similar problem, but with different names and labels to change. In this case you should not change the names of your data but the names in the code that I had indicated).
Instead I was referring to the second message in which I pointed out that I had a precise idea of where the problem was.

So I wrote:

"SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020" contains the substring "elda".

[Anonymous]

Hi @lboldrino  I have an idea and is related to the fact that

"SAP PSA: Status inkorrekt - Zieldatum: 29.1.2020" contains the substring "elda".

the other case ELDA,ELDA is similar to the case mbg,ELDA and depends from fact that the [col] text can contains many (2 in this cases) searchwords.

you should change strategy if you want to to avoid these situations