Forum Discussion
Loop table to add column and values from another table
I am a new user of power query, please forgive me for the following questions:
Example 1:
I have a table A(According field Number of vehicles descending).The table is not sorted initially.
| Car ID | Number of vehicles |
| ------ | ------------------ |
| ED2399 | 3 |
| AUN203 | 2 |
| EJ3263 | 1 |
| EG0209 | 1 |
I have a table B(Group Column value = Group({"Model", "Tax ID"}, RowCount), According field Group descending).The table is not grouped and sorted initially.
| Model | Tax ID | Price | Group |
| ------------- | ---------- | ----- | ----- |
| Panamera A311 | 8703236110 | 2130 | 4 |
| Panamera A311 | 8703236110 | 2130 | 4 |
| Panamera A311 | 8703236110 | 2085 | 4 |
| Panamera A311 | 8703236110 | 2170 | 4 |
| Macan A171 | 8703236310 | 1660 | 2 |
| Macan A171 | 8703236310 | 1660 | 2 |
| Cayenne A361 | 8703234310 | 2140 | 1 |
Table A sum(Number of vehicles) = Table B total Row Count. Field Number of vehicles value Match Field Group value.
Desired Outcome:
| Model | Tax ID | Price | Group | Car ID |
| ------------- | ---------- | ----- | ----- | ------ |
| Panamera A311 | 8703236110 | 2130 | 4 | ED2399 |
| Panamera A311 | 8703236110 | 2130 | 4 | ED2399 |
| Panamera A311 | 8703236110 | 2085 | 4 | ED2399 |
| Panamera A311 | 8703236110 | 2170 | 4 | EJ3263 |
| Macan A171 | 8703236310 | 1660 | 2 | AUN203 |
| Macan A171 | 8703236310 | 1660 | 2 | AUN203 |
| Cayenne A361 | 8703234310 | 2140 | 1 | EG0209 |
Example 2:
I have a table A(According field Number of vehicles descending).The table is not sorted initially.
| Car ID | Number of vehicles |
| ------ | ------------------ |
| ED2399 | 4 |
| AUN203 | 3 |
I have a table B(Group Column value = Group({"Model", "Tax ID"}, RowCount), According field Group descending).The table is not grouped and sorted initially.
| Model | Tax ID | Price | Group |
| ------------- | ---------- | ----- | ----- |
| Panamera A311 | 8703236110 | 2130 | 2 |
| Panamera A311 | 8703236110 | 2130 | 2 |
| Macan A171 | 8703236310 | 1660 | 2 |
| Macan A171 | 8703236310 | 1660 | 2 |
| Panamera A312 | 8703234310 | 2140 | 1 |
| Boxster A422 | 8703234110 | 2085 | 1 |
| Cayenne A361 | 8703236310 | 2140 | 1 |
Table A sum(Number of vehicles) = Table B total Row Count. Field Number of vehicles value Match Field Group value.
Desired Outcome:
| Model | Tax ID | Price | Group | Car ID |
| ------------- | ---------- | ----- | ----- | ------ |
| Panamera A311 | 8703236110 | 2130 | 2 | ED2399 |
| Panamera A311 | 8703236110 | 2130 | 2 | ED2399 |
| Macan A171 | 8703236310 | 1660 | 2 | ED2399 |
| Macan A171 | 8703236310 | 1660 | 2 | ED2399 |
| Panamera A312 | 8703234310 | 2140 | 1 | AUN203 |
| Boxster A422 | 8703234110 | 2085 | 1 | AUN203 |
| Cayenne A361 | 8703236310 | 2140 | 1 | AUN203 |
Please help me, thanks!
2 Replies
For your example 1 what is the allocation logic? Does a Car ID have to fit completely into a group?
Example 2 seems to ignore the last two cars. Not clear what the allocation logic should be.
Note: Power BI is not a resource planning tool.
