Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
msmays5
Helper II
Helper II

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 exist is variable, but I do have a List FactList[Fact Name] that contains each fact included in the DataTable

MarketProductPeriodFact 1Fact 2Fact 3
Market 1Product 1Period 1   
Market 1Product 1Period 2   
Market 1Product 2Period 1   
Market 1Product 2Period 2   
Market 2Product 1Period 1   
Market 2Product 1Period 2   
Market 2Product 2Period 1   
Market 2Product 2Period 2   

 

For each fact in FactList[Fact Name], I'd like to add 1 column to my DataTable. This column should subtract Period 1 - Period 2 for the given fact.

 

My attempt at figuring this out is as follows:

 

 

 

 

 

ListOfDimensions = Record.ToList([A = "Market", B = "Product", C = "Period"]),
ListOfFacts = FactList[FactName],
AddMergeColumn = Table.AddColumns(DataTable, "NonPeriodLookup", each Text.Combine({[Market],[Product]}, ""), type text),
ListOfNonFacts = List.Union({List.Select(ListOfDimensions , each _ <> "Period"), {"NonPerLookup"}})),

//Table with only Period 1 rows, facts names appended with " P1"
Period1 = Table.RemoveColumns(
  Table.TransformColumnNames( 
    Table.SelectRows(AddMergeColumn , each [Period] = "Period 1"),
      each if List.Contains(ListOfFacts, _) then _ & " P1" else _
    ),
  "Period"
),
//Table with only Period 2 rows, facts names appended with " P2"
Period2 = Table.RemoveColumns(
  Table.TransformColumnNames( 
    Table.SelectRows(AddMergeColumn , each [Period] = "Period 2"),
      each if List.Contains(ListOfFacts, _) then _ & " P2" else _
    ),
  "Period"
),

#"Merged Queries2" = Table.NestedJoin(Period1 ,{"NonPeriodLookup"},Period2, {"NonPeriodLookup"},"MergedP2",JoinKind.LeftOuter),

ListOfColumnsToExpand = Table.ColumnNames(Table.RemoveColumns(Period2, ListOfNonFacts)),
#"Expanded P2" = Table.ExpandTableColumn(#"Merged Queries2","MergedP2",ListOfColumnsToExpand),

AccumulateFacts = 
  List.Accumulate(ListOfFacts, #"Expanded P2", 
    (state, current) => Table.AddColumn(state, current & " Chg", each [P1] - [P2]
  )
)

in

AccumulateFacts 

 

 

 

 

 

 

This is my first real attempt at using List.Accumulate, but I think(?) I'm doing it correctly. The issue I'm running into is that I can't figure out how to refernce the two columns I want to subtract. In the above where I have [P1] - [P2], I really mean "[" & current & " P1]" - "[" & current & " P2]", but I don't know how to correctly reference columns dynmically.

 

Any help would be greatly appreciated. Thanks!

4 REPLIES 4
Greg_Deckler
Super User
Super User

@msmays5 Not sure if easier or harder, but definitely pretty simple in DAX. But, I know that @ImkeF has solved this and probably @edhans has as well. I'm sure they will be along shortly or maybe @HotChilli , quite a few good Power Query people on the boards.

 

Just for reference (FYI only), one DAX method is the MTBF pattern. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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.

 

 

@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?

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?

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors