Forum Discussion
Rudz
5 years agoKudo Collector
Merge tables using wildcards
I have a small fact table of about 10,000 records. Based on 5 columns in each row, I need to add 3 extra columns from a small dimension table of around 70 records. The problem is that the rules i...
- 5 years ago
let Fact = Excel.CurrentWorkbook(){[Name="Fact"]}[Content], Dim = Excel.CurrentWorkbook(){[Name="Dim"]}[Content], fx=(t1,t2)=> let a=Text.From(t2), b=Text.From(t1), c=if Text.End(a,1)="*" then Text.StartsWith(b??"",Text.Start(a,Text.Length(a)-1)) else a=b in if a="*" then true else c, Custom1 = Table.AddColumn( Fact, "n", each let col=Table.ColumnNames(Fact), b=Table.SelectRows( Dim, (x)=>List.AllTrue(List.Transform(col,(y)=>fx(Record.Field(_,y),Record.Field(x,y)))) ) in if b{0}?=null then {null,null,null} else Record.ToList(Record.SelectFields(b{0},{"Result1","Result2","Result3"})) ), Custom2 = Table.SplitColumn(Custom1,"n",each _,{"Result1","Result2","Result3"}) in Custom2
wdx223_Daniel
5 years agoCommunity Champion
let
Fact = Excel.CurrentWorkbook(){[Name="Fact"]}[Content],
Dim = Excel.CurrentWorkbook(){[Name="Dim"]}[Content],
fx=(t1,t2)=>
let
a=Text.From(t2),
b=Text.From(t1),
c=if Text.End(a,1)="*" then Text.StartsWith(b??"",Text.Start(a,Text.Length(a)-1)) else a=b
in if a="*" then true else c,
Custom1 = Table.AddColumn(
Fact,
"n",
each let
col=Table.ColumnNames(Fact),
b=Table.SelectRows(
Dim,
(x)=>List.AllTrue(List.Transform(col,(y)=>fx(Record.Field(_,y),Record.Field(x,y))))
)
in if b{0}?=null
then {null,null,null}
else Record.ToList(Record.SelectFields(b{0},{"Result1","Result2","Result3"}))
),
Custom2 = Table.SplitColumn(Custom1,"n",each _,{"Result1","Result2","Result3"})
in
Custom2