Forum Discussion

cs141005's avatar
cs141005
Regular Visitor
5 years ago
Solved

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 betwe...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
5 years ago

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's avatar
cs141005
Regular Visitor
5 years ago

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