Returning data from a spreadsheet to the database.

[brunodsg]

Hi! I have a customer base and each one has different payment conditions, some pay daily, others weekly, others every two weeks and others monthly, but this data is not in my main database but in another Excel spreadsheet.

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 🙂

[123abc · Accepted solution]

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:

1. Load both your main database table and the Excel spreadsheet into Power BI.

2. Ensure that there is a relationship established between the two tables based on the customer name.

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

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