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 |
I'am sorry for my english skills, i will really pleased to hear you for any solution from power query or even from dax in a custom column
