The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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