Trying to create a new table based on the imported data. Inthe new table I want to create 4 calc columns based on the row content.
I have value column but want to create 4 calc columns based on Estimate and PnL Line.
How do I do that? I am also open for editing in the query editor
Go to Solution.
I have solved it. The best/easiest/quickest way was to use the "conditional column" function in Query Editor.
View solution in original post
Another option is to create a new virtual table with aggregation
AggregatedPayments = SUMMARIZE (CALCULATETABLE ('Payments',Payments[TransactionReference] <> ""),'Payments'[TransactionReference],"TotalPaidToInvoice",SUM ( 'Payments'[AllocationAmount] ))
In my example, I wanted a table showing sum of payments per Transaction, but only where the transaction reference was not blank
This can be broken into two steps...
CALCULATETABLE ('Payments',Payments[TransactionReference] <> "")
This creates a dataset based on the Payments table, filtering out blank references
the table is the the input for a SUMMARIZE function, which performs this aggregation
SUMMARIZE ([Result of CALCULATETABLE],
'Payments'[TransactionReference],"TotalPaidToInvoice",SUM ( 'Payments'[AllocationAmount] ))
This gives me the reference, and the sum of 'Allocation', calling the new field 'Total Paid To Invoice'
This then appears as a new dataset for me to work with (and can create new relationships based on it)
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.