Forum Discussion
Returning data from a spreadsheet to the database.
I need to add a new column in my main database that returns data according to this other Excel spreadsheet, following this example:
Main database
| Customer base | Payment Condition (New Column) |
| A | ? Dax Command |
| B | ? Dax Command |
| B | ? Dax Command |
A | ? Dax Command |
C | ? Dax Command |
D | ? Dax Command |
C | ? Dax Command |
Excel Spredsheet
| Customer | Payment Condition |
| A | Daily |
| B | Weekly |
| C | Daily |
| D | Monthly |
Summary:
If the customer name matches between the main database and the Excel spreadsheet, I need the data from the "Payment Condition" column to be filled into the new column in my database.
Thanks in advance 🙂
In Power BI, you can achieve this using DAX by creating a calculated column in your main database table. The calculated column will use the RELATED function to look up the corresponding payment condition from the Excel spreadsheet based on the customer name.
Here's how you can do it step by step:
Load both your main database table and the Excel spreadsheet into Power BI.
Ensure that there is a relationship established between the two tables based on the customer name.
Create a new calculated column in your main database table using the following DAX formula:
Payment Condition = RELATED('Excel Spreadsheet'[Payment Condition])
Replace 'Excel Spreadsheet' with the name of your Excel spreadsheet table in Power BI.
This formula uses the RELATED function to retrieve the payment condition from the related row in the Excel spreadsheet table based on the matching customer name.
- Once you've created the calculated column, Power BI will automatically populate it with the payment conditions for each customer based on the matching customer names between the main database table and the Excel spreadsheet.
After performing these steps, your main database table should have a new column called "Payment Condition" populated with the data from the Excel spreadsheet, matching each customer's payment condition.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
1 Reply
- 123abc
Community Champion
In Power BI, you can achieve this using DAX by creating a calculated column in your main database table. The calculated column will use the RELATED function to look up the corresponding payment condition from the Excel spreadsheet based on the customer name.
Here's how you can do it step by step:
Load both your main database table and the Excel spreadsheet into Power BI.
Ensure that there is a relationship established between the two tables based on the customer name.
Create a new calculated column in your main database table using the following DAX formula:
Payment Condition = RELATED('Excel Spreadsheet'[Payment Condition])
Replace 'Excel Spreadsheet' with the name of your Excel spreadsheet table in Power BI.
This formula uses the RELATED function to retrieve the payment condition from the related row in the Excel spreadsheet table based on the matching customer name.
- Once you've created the calculated column, Power BI will automatically populate it with the payment conditions for each customer based on the matching customer names between the main database table and the Excel spreadsheet.
After performing these steps, your main database table should have a new column called "Payment Condition" populated with the data from the Excel spreadsheet, matching each customer's payment condition.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.