Forum Discussion

Rudz's avatar
Rudz
Kudo Collector
5 years ago
Solved

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...
  • wdx223_Daniel's avatar
    5 years ago

    Rudz 

    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