Forum Discussion

msmays5's avatar
msmays5
Helper II
5 years ago

Add Multiple Columns from List that Have Standard Mathematical Operation

First, let me acknowledge that getting my result might be easier in DAX, but I would like to accomplish this in PQ for a host of reasons.   I have a DataTable below. The number of Fact columns that...
Need Help
HotChilli's avatar
HotChilli
Community Champion
5 years ago

Here's some code to make the provided algorithm work:

#"Added Index" = Table.AddIndexColumn(#"Expanded P2", "Index", 0, 1, Int64.Type),

AccumulateFacts = 
   List.Accumulate(ListOfFacts, #"Added Index", 
    (state, current) => Table.AddColumn(state, current & " Chg", each Number.FromText(Table.Column(#"Added Index", current & " P1"){[Index]}) - Number.FromText(Table.Column(#"Added Index", current & " P2"){[Index]}) 
  )
)

I added an Index to get a quick way of identifying the row.  Pretty sure the code can be made to work without it but I'll leave that as an exercise.

 

Also, the code provided by the OP didn't pass syntax check.

 

 

msmays5's avatar
msmays5
Helper II
5 years ago

HotChilli @Thanks for the reply, and sorry for the delayed response. I'm actually not sure if it worked because I received a PQ error saying I ran out of memory (before that step, PQ said it was loading 88 MB). Any ideas why that happens?

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    I hope that you test your algorithms with small, representative datasets before testing on your real data.

     

    You have options (make sure you have 64 bit powerbi -it would be unusual not to but I thought I'd mention it)

    Make sure you have enough memory.  Power Query will try and use all the memory it needs for complex operations, it's like climbing up the mountain.  Once it's over the top, happy days. 

    Re-write the algorithm to avoid the problem.  This may be easier said than done but there's always another way to do it..

     

    Edit: What's your data source?