Forum Discussion

spr_RK's avatar
spr_RK
Frequent Visitor
4 years ago
Solved

List.Accumulate conditional replace value in dynamic columns from lookup table

Hello all - I have 3 tasks I have been able to accomplish on a small sample set of data, but with my full dataset the refresh never finishes due to what I am assuming is a performance issue on the la...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

     

    let
    Sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    SpecificLists = Table.Buffer(Table.Group(Excel.CurrentWorkbook(){[Name="SpecificLists"]}[Content],"Code",{"n",each _})),
    Columns = Table.Buffer(Excel.CurrentWorkbook(){[Name="Columns"]}[Content]),
    DashboardAssignments = Table.Buffer(Excel.CurrentWorkbook(){[Name="DashboardAssignments"]}[Content]),
    Custom1 = Table.FromRecords(
                                Table.TransformRows(
                                                    Sales,
                                                    each let a=SpecificLists{[Code=[Code]]}?[n]?,
                                                             b=Record.ToTable(_)
                                                         in Record.FromTable(
                                                                             Table.ReplaceValue(
                                                                                                b,
                                                                                                each [Name],
                                                                                                each List.Contains(Columns[Dashboard],[Name]),
                                                                                                (x,y,z)=>if z
                                                                                                         then DashboardAssignments{[Account_Dashboard=(a{[Dashboard=y]}?[Updated Name]? ??x)&"|"&y]}?[Name]?
                                                                                                         else x,
                                                                                                {"Value"}
                                                                                               )
                                                                            )
                                                   )
                               )
in
    Custom1

     

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
4 years ago

 

let
    Sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    SpecificLists = Table.Buffer(Table.Group(Excel.CurrentWorkbook(){[Name="SpecificLists"]}[Content],"Code",{"n",each _})),
    Columns = Table.Buffer(Excel.CurrentWorkbook(){[Name="Columns"]}[Content]),
    DashboardAssignments = Table.Buffer(Excel.CurrentWorkbook(){[Name="DashboardAssignments"]}[Content]),
    Custom1 = Table.FromRecords(
                                Table.TransformRows(
                                                    Sales,
                                                    each let a=SpecificLists{[Code=[Code]]}?[n]?,
                                                             b=Record.ToTable(_)
                                                         in Record.FromTable(
                                                                             Table.ReplaceValue(
                                                                                                b,
                                                                                                each [Name],
                                                                                                each List.Contains(Columns[Dashboard],[Name]),
                                                                                                (x,y,z)=>if z
                                                                                                         then DashboardAssignments{[Account_Dashboard=(a{[Dashboard=y]}?[Updated Name]? ??x)&"|"&y]}?[Name]?
                                                                                                         else x,
                                                                                                {"Value"}
                                                                                               )
                                                                            )
                                                   )
                               )
in
    Custom1

 

  • spr_RK's avatar
    spr_RK
    Frequent Visitor
    4 years ago

    This worked like a dream! Refresh is manageable on the large dataset at about 1.5 hours, and so far the results look correct. Thank you for the quick response - now for me to dissect your answer to understand everything in your code.

  • spr_RK's avatar
    spr_RK
    Frequent Visitor
    4 years ago

    So it took me a while, but I think I understand what each piece is doing (although it needs more time to really sink in) and all I can say is you've opened up a whole new world of M language for me, haha. Thanks again!