Forum Discussion
Splitting data out over a predefined percentage and using that data for new calculations
KatrienVds
No need to select all columns. We will follow your strategy. Use this code to generate the appended header table.Order Header 2 = VAR Owner1Table = SELECTCOLUMNS ( 'Splitted orders 1', "@Order No", 'Splitted orders 1'[Order No], "@Owner", 'Splitted orders 1'[Owner 1], "@Percentage", 'Splitted orders 1'[Percentage] ) VAR Owner2Table = SELECTCOLUMNS ( 'Splitted orders 1', "@Order No", 'Splitted orders 1'[Order No], "@Owner", 'Splitted orders 1'[Owner 2], "@Percentage", 1 - 'Splitted orders 1'[Percentage] ) VAR SplittedOrders = UNION ( Owner1Table, Owner2Table ) VAR OrderHeader = SELECTCOLUMNS ( 'Order Header', "Order No", 'Order Header'[Order No], "Owner", 'Order Header'[Owner] ) VAR NotSplittedOrders = ADDCOLUMNS ( FILTER ( OrderHeader, NOT ( [Order No] IN VALUES ( 'Splitted orders 1'[Order No] ) ) ), "Percentage", 1 ) VAR Result = UNION ( NotSplittedOrders, SplittedOrders ) RETURN ResultThen make tthis table in between the two tables exactly as you have suggested
Community ChampionHi KatrienVds
The following code shall produce the correct header table. You can create a many-many relationship between 'Order Header 2'[Order No] and 'Order Line'[Order No]. Having the Percentage column, it shall be easy to retrieve the correct percentage of each project/owner combination either 100% or splitted percentage
Order Header 2 =
VAR Owner1Table =
SELECTCOLUMNS (
'Splitted orders 1',
"@Order No", 'Splitted orders 1'[Order No],
"@Owner", 'Splitted orders 1'[Owner 1],
"@Percentage", 'Splitted orders 1'[Percentage]
)
VAR Owner2Table =
SELECTCOLUMNS (
'Splitted orders 1',
"@Order No", 'Splitted orders 1'[Order No],
"@Owner", 'Splitted orders 1'[Owner 2],
"@Percentage", 100 - 'Splitted orders 1'[Percentage]
)
VAR SplittedOrders =
UNION ( Owner1Table, Owner2Table )
VAR NotSplittedOrders =
ADDCOLUMNS ( FILTER ( 'Order Header', NOT ( 'Order Header'[Order No] IN VALUES ( 'Splitted orders 1'[Order No] ) ) ), "Percentage", 100 )
VAR Result =
UNION ( NotSplittedOrders, SplittedOrders )
RETURN
Result
Hi tamerj1
Thank you so much for all yout input!
I think we're almost there, but something is still up with the last UNIONs.
I keep getting the "All table arguments of the function UNION should have the same amount of columns." message.
VAR NotSplittedOrders =
ADDCOLUMNS ( FILTER ( 'Order Header', NOT ( 'Order Header'[Order No] IN VALUES ( 'Splitted orders 1'[Order No] ) ) ), "Percentage", 100 )
If I understood the code correct it filters my headers on all order lines that are not present in the splitted order lines and afterwards merges them. So the union above would result in a far larger table than the union below.
VAR SplittedOrders =
UNION ( Owner1Table, Owner2Table )
As in table 'Splitted orders 1' I only have 3 columns wheras in 'Order header' there are about 50 or more.So I'd need to join my Splitted headers over the existing values in the Order headers too. So my splitted orders get enriched with the missing data.
Or have to extract the three columns from the order header and put this table inbetween my original header with a one to many and then the new header with a many to many to the lines. But this looks a little cumbersome to me.
- tamerj1
KatrienVds
"Or have to extract the three columns from the order header and put this table inbetween my original header with a one to many and then the new header with a many to many to the lines. But this looks a little cumbersome to me. "
Yes great idea! that would be the best option in my openion. About to give it a try like this.
Managed to get the summarized table inbetween. Fingers crossed.- tamerj1
KatrienVds
No need to select all columns. We will follow your strategy. Use this code to generate the appended header table.Order Header 2 = VAR Owner1Table = SELECTCOLUMNS ( 'Splitted orders 1', "@Order No", 'Splitted orders 1'[Order No], "@Owner", 'Splitted orders 1'[Owner 1], "@Percentage", 'Splitted orders 1'[Percentage] ) VAR Owner2Table = SELECTCOLUMNS ( 'Splitted orders 1', "@Order No", 'Splitted orders 1'[Order No], "@Owner", 'Splitted orders 1'[Owner 2], "@Percentage", 1 - 'Splitted orders 1'[Percentage] ) VAR SplittedOrders = UNION ( Owner1Table, Owner2Table ) VAR OrderHeader = SELECTCOLUMNS ( 'Order Header', "Order No", 'Order Header'[Order No], "Owner", 'Order Header'[Owner] ) VAR NotSplittedOrders = ADDCOLUMNS ( FILTER ( OrderHeader, NOT ( [Order No] IN VALUES ( 'Splitted orders 1'[Order No] ) ) ), "Percentage", 1 ) VAR Result = UNION ( NotSplittedOrders, SplittedOrders ) RETURN ResultThen make tthis table in between the two tables exactly as you have suggested
Been playing with this solution for a few days now and so far it seems to work.
Thank you 😊- tamerj1
Hi KatrienVds
Regarding the error, yes you should have the same number of columns, this is why I asked you what columns you have and what columns you want to keep. You can share ascreenshot to help you further.Regarding the code, yes you are right. The idea is to filter the header table removing the order that exist in the splitted table then append the filtered table with the splitted one. This way we get the complete data in one table.
Here is a sample file for your reference. I went further with caclulations to explain to you next steps. https://www.dropbox.com/t/Y24cbKsw85In4BMV Hi tamerj1
I will have to keep almost all of the columns from my order table, that's why I was asking if there isn't some sort of select all function like in SQL.On a different note,
I think a normal append won't help because in the Splitted orders we only get 3 values.
All other info such as sell-to company, bill-to company, delivery terms, payment terms, delivery location (street, postcode, city, country), order date, due date, etc are all being stored in the header. To have an idea of what all gets stored in the header: https://dynamicsdocs.com/nav/2017/w1/table/sales-header
If I'd have to add all lets say columns manually to the other table they'll result in NULL values and I'd have to do a lookup for each in my original table? Which in this case again looks quite cumbersome for something SQL would fix with a JOIN operation.
So either I'll have to make a mini table and add it in there or I'd have to find way to use working JOIN operations instead of UNIONS.