Forum Discussion

Sima007's avatar
Sima007
New Member
2 years ago
Solved

Assign first value available from an other table

Hello everybody

I have 2 tables :

  1. Products table with product code, equipment type, index wich is a priority, and count of product : if a product need 2 places so i have the same product twice. (it's a dax calculated table)
  2. List of storage address by equipment type and also indexed by priority.

For each row in Product Table, i need to assign first address found and flag the address to avoid assigning to another row. and if a product has multiple row, so the first occurence will take the first non assigned address, and the other occurence will take the first non assigned address in the same aisle and side.

 

 


Hope it's clear

Thanks 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Sima007 ,

     

    Yes, you can.

     

    Before you begin, you need to install Python and Pandas.

     

    In Power BI desktop, specify the installation path of Python.

     

    File > Options and settings > Options > Python scripting

     

    In the Power Query editor, select and enter the Python code for the calculation.

     

    Transform > Run Python script

     

    There are some limitations for queries contained in Python scripts created in Power Query:

     

    All Python data source settings must be set to Public, and all other steps in queries created in the Power Query Editor must be set to Public.

     

    To enable scheduled refresh for a Python visual or dataset, you need to enable Scheduled Refresh and install Personal Gateway on the computer where the workbook and Python installation are stored.

     

    Nested tables (tables within tables) are not currently supported.

     

    For more information see:

    Use Python in Power BI Power Query Editor - Power BI | Microsoft Learn

     

    If you have any further questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

4 Replies

  • Sima007's avatar
    Sima007
    New Member
    2 years ago

    Hello, Can any one help please ? if i had to do it in Excel i would do a loop like this. but i don't really know how to do it with Dax

    N = product_table_row_count
k = Address_table_row_count
Addcolumns(Address_table,[FLAg]) 
Address_table[Flag] = 0 //initialized by 0
for i = 1 to n
if Product_table[Occurence] = 1 // first occurence of product
for j = 1 to k
if AddressTable(j,"Flag")=0 then
Product_table(i,Address) = Address_table(j,Address])
AddressTable(j,"Flag")=1
end if
next j
else
for j = 1 to k
>> find the address beside the first occurence off product with flag = 0
AddressTable(j,"Flag")=1
next j
next i

     thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Sima007 ,

     

    I tried it and found it difficult to achieve this requirement.

     

    Your requirement needs to be checked line by line to determine whether the conditions are met - that is, addresses cannot be repeated.

     

    My suggestion is that if possible, consider assigning addresses to products based on your criteria in a database or using a scripting language like Python.

     

    If you have any further questions please feel free to contact me.

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    • Sima007's avatar
      Sima007
      New Member
      2 years ago

      Hello Anonymous

      is it possible to use Python in power bi to do this calculation?

      Thanks

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Sima007 ,

         

        Yes, you can.

         

        Before you begin, you need to install Python and Pandas.

         

        In Power BI desktop, specify the installation path of Python.

         

        File > Options and settings > Options > Python scripting

         

        In the Power Query editor, select and enter the Python code for the calculation.

         

        Transform > Run Python script

         

        There are some limitations for queries contained in Python scripts created in Power Query:

         

        All Python data source settings must be set to Public, and all other steps in queries created in the Power Query Editor must be set to Public.

         

        To enable scheduled refresh for a Python visual or dataset, you need to enable Scheduled Refresh and install Personal Gateway on the computer where the workbook and Python installation are stored.

         

        Nested tables (tables within tables) are not currently supported.

         

        For more information see:

        Use Python in Power BI Power Query Editor - Power BI | Microsoft Learn

         

        If you have any further questions please feel free to contact me.

         

        Best Regards,
        Yang
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!