how to retrieve selected values from another table (with no relationship)

[cs141005]

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

[V-pazhen-msft · Accepted solution]

@cs141005 

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

[VahidDM]

@cs141005 

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://support.microsoft.com/en-us/office/replace-values-power-query-28256517-f1e9-4dc3-832f-45786e...

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  !!

[cs141005]

Hi @VahidDM ,

The values from Sales table will be updated from a database. So i would like the Sales BR table to retrieve selected values from Sales table.

I hope I made clearer what I want to achieve.

[Pragati11]

Hi @cs141005 ,

I am assuming you have TYPE column common in both the tables, so what you can do is you can cobine these 2 tables using MERGE option in Power Query Editor in Power BI.

Check the link here on how to achieve this:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data#combine-querie...

Thanks,

Pragati

[cs141005]

I do not want to merge the tables but to retrieve selected values ​​from the first (Sales table) to the second (Sales BR)

[Pragati11]

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

[cs141005]

Hi @ Pragati11,

Thank you very much for your answer 🙂

if I understood correctly, with the merge method you suggested to me, I will have a new table with all the columns of the 1st and in addition of the 2nd table, right?

what I want to achieve is to "fill" (with prices) the BR sales table based on the 1st table. When the types of the first table - "Sales" (e.g. Bussiness Value) matches the types of the second table - Sales BR (e.g. BV), then the 2nd table retrieves these values.

Thanks