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
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.
- 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 😊