Forum Discussion
how to retrieve selected values from another table (with no relationship)
Hi all,
I have 2 tables : Sales and Sales BR.
My purpose is to fill Sales BR table with selected values from Sales table. Also i want to make some calculations..
- There is no relationship between 2 tables.
maybe i should create calculated columns, but im new to DAX syntax and i dont know how to do it.
Any assistance would be greatly appreciated.
Thanks
****Update***
my whole idea is for the Sales table to be updated from a database and then Sales BR table to retrieve selected values (from Sales table).
Here are the steps you can follow：
1. Enter the Power query through Transform data, add Index to the two tables respectively, and select Add column – Index Column.
2. Create calculated column.
Q1 = IF( 'Sales BR'[Index] in SELECTCOLUMNS('Sales',"1",'Sales'[Index]), SUMX(FILTER(ALL('Sales'),'Sales'[Index]='Sales BR'[Index]),[Q1]), DIVIDE( SUMX(FILTER(ALL(Sales),'Sales'[type]="new users"),[Q1]), SUMX(FILTER(ALL(Sales),'Sales'[type]="users"),[Q1]) ))Q2 = IF( 'Sales BR'[Index] in SELECTCOLUMNS('Sales',"1",'Sales'[Index]), SUMX(FILTER(ALL('Sales'),'Sales'[Index]='Sales BR'[Index]),[Q2]), DIVIDE( SUMX(FILTER(ALL(Sales),'Sales'[type]="new users"),[Q2]), SUMX(FILTER(ALL(Sales),'Sales'[type]="users"),[Q2]) ))3. Result:
Best Regards,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
7 Replies
I do not want to merge the tables but to retrieve selected values from the first (Sales table) to the second (Sales BR)
- Pragati11
Super User
HI cs141005 ,
Merging doesn't mean what t says always. Merging needs the kind of join you want to do like you do in SQL database.
Duplicate your 1st table and then use merge with the other table, may be with a LEFT JOIN.
This will create a new table, with all the columns from 1st table and the additional required columns from the 2nd table.
If the process is still not clear, attach files for your sample data, so that I can add steps to achieve what is required.
You can add some sample data in a file and upload the to dropbox and share the dropbox link here. make sure you are removing any sensitive information from your data.
Thanks,
Pragati
- VahidDM
You can use "Replace Values" in the power query to change those line names, and you don't need to have another table (even if you need another table, you can create a duplicate table and change the values there).
check these links:
https://yodalearning.com/tutorials/learn-how-replace-values-power-query/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your Kudos !!
Here are the steps you can follow：
1. Enter the Power query through Transform data, add Index to the two tables respectively, and select Add column – Index Column.
2. Create calculated column.
Q1 = IF( 'Sales BR'[Index] in SELECTCOLUMNS('Sales',"1",'Sales'[Index]), SUMX(FILTER(ALL('Sales'),'Sales'[Index]='Sales BR'[Index]),[Q1]), DIVIDE( SUMX(FILTER(ALL(Sales),'Sales'[type]="new users"),[Q1]), SUMX(FILTER(ALL(Sales),'Sales'[type]="users"),[Q1]) ))Q2 = IF( 'Sales BR'[Index] in SELECTCOLUMNS('Sales',"1",'Sales'[Index]), SUMX(FILTER(ALL('Sales'),'Sales'[Index]='Sales BR'[Index]),[Q2]), DIVIDE( SUMX(FILTER(ALL(Sales),'Sales'[type]="new users"),[Q2]), SUMX(FILTER(ALL(Sales),'Sales'[type]="users"),[Q2]) ))3. Result:
Best Regards,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
