Conatenate all "alias" of matching values

EnzoVAUTOR · ‎03-06-2023

Hello,

i'm an old french VBA lover who hope to get the train of Power Bi.

I'm looking for a Power query or a DAX functions which permit me to match values which could have wildcards.

I Have to table the first one called : TabCorr; which permit me to get from an accounting codification/concatenation to get segment titles.

TabCorr

NomFocus	ConcatUA
GlobalStéAàStéB1	50~??????~C000362
GlobalStéAàStéB2	50~??????~C000502
GlobalStéAàStéB3	50~??????~F026345
VentesStéAàStéB1	50~707???~C000362
AchatsStéAàStéB1	50~607???~F026345
AchatsStéAàStéB1_8.5	50~607020~C000362
AchatsStéAàStéB1_2.1	50~607021~C000362
VentesMarchandises8.5HHHàMMX	50~707010~C000362
VentesMarchandisesExoHHHàMMX	50~707011~C000362

My actual power query advenced editor for this table look like that :
let
Source = Excel.Workbook(File.Contents("C:\Users\hmenva\OneDrive - HOHIOHEN Automobile\InterCoSté\TableCorrInterCo.xlsx"), null, true),
TabCorr_Table = Source{[Item="TabCorrVentesEtAchats",Kind="Table"]}[Data],
#"Type modifié" = Table.TransformColumnTypes(TabCorr_Table,{{"NomFocus", type text}, {"CodeStéUA", type text}, {"NumCompteUA", type text}, {"ContrePartieUA", type text}, {"ConcatUA", type text}, {"CodeStéUB", Int64.Type}, {"NumCompteUB", type text}, {"ContrePartieUB", type text}, {"ConcatUB", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Type modifié"," ","",Replacer.ReplaceText,{"ConcatUA"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Id", Int64.Type}})
in
#"Changed Type"

And a table

ListOfEligiblesECPIE which the list of the accounting rows which match with the list of ConcatUA

ConcCatUniv	ECPIE émetteur
50~607020~C000362	80523654
50~607020~C000362	58963222
50~707???~C000362	55555555

with this advanced editor code :

let
Source = Table.Combine({GL_HHH, GL_MMX, GL_CGA, GL_MSP, GL_OIPA}),
FocusOnNeededColumns = Table.Distinct(Table.SelectColumns(Source,{"ConcatUniv","ECPIE"})),
EligibleList = Table.SelectRows(FocusOnNeededColumns, each (List.Contains(SearchingEligibleConcatEmetteur, [ConcatUniv])=true)),
#"Renamed Columns" = Table.RenameColumns(EligibleList,{{"ECPIE", "ECPIEémetteur"}}),
AddAllFocusNamesPossibilities = Table.AddColumn(#"Renamed Columns","FocusPossibles", each List.Accumulate(Table.ToList(Table.FromColumns({"ConcatUA","NomFocus"}),Combiner.CombineTextByDelimiter(",")),"",(state,current)=>
if Text.Contains([Column 2], current)
then current&"/"&state
else state
))
in
AddAllFocusNamesPossibilities

What i'm trying to ask is : to add a list or simply a concatenation into a new column called ListOfEligiblesECPIE[FocusPossibles] to this second table which will give me all the TabCorr[NomFocus] when TabCorr[ConcatUA] is matching even partially due to wildcards with ListOfEligiblesECPIE[ConcatUniv].

Something like that :

ListOfEligiblesECPIE

ConcCatUniv	ECPIE émetteur	FocusPossibles
50~607020~C000362	80523654	GlobalStéAàStéB1, AchatsStéAàStéB1,AchatsStéAàStéB1_8.5
50~607021~C000362	58963222	GlobalStéAàStéB1, AchatsStéAàStéB1,AchatsStéAàStéB1_2.1
50~707010~C000362	707456123	GlobalStéAàStéB1, VentesStéAàStéB1, VentesMarchandises8.5HHHàMMX
50~707011~C000362	707123456	GlobalStéAàStéB1, VentesStéAàStéB1, VentesMarchandisesExoHHHàMMX