Conditional import from Excel

[Anonymous]

Hi,

When importing data from an excel file, I need to substitute some values with alternative values form another column. Is there a possibility in PowerBI import to do this? 

[BA_Pete]

Hi @Anonymous ,

This isn't something that can be done during the data import per se, but it can certainly be done before the data is applied to the data model for reporting.

The data flow in Power BI Desktop in its most basic form is:

Data Source (Excel in your case) >> Power Query (do transformations) >> Data Model (define relationships) >> Report (write measures and visualise data).

What you're describing would be a transformation step that would be done in Power Query.

If you need help doing this, post an example of your data in this thread with a detailed description of what you want done and expected end result.

Pete

[Anonymous]

Hi, Pete, pls check this sample data:

1. Incoming data shows that some POSs maybe resold to an end-customer companies:

item#	Purchasing channel	End Customer	Sales volume
1	Star Inc.	Not Applicable	100
2	Cube Inc.	PPM Co.	20
3	Alliance Co.	ABC Inc.	500
4	Star Inc.	PPM Co.	50
5	Star Inc.	Not Applicable	45
6	Cube Inc.	Not Applicable	20
7	Alliance Co.	ABC Inc.	500
8	Alliance Co.	ABC Inc.	20
9	Alliance Co.	PPM Co.	300
10	Star Inc.	Not Applicable	45

2. after processing the highlighted items in red has to be substituted by the items in 2d column:

item#	Purchasing channel	End Customer	Sales volume
1	Star Inc.	Star Inc.	100
2	Cube Inc.	PPM Co.	20
3	Alliance Co.	ABC Inc.	500
4	Star Inc.	PPM Co.	50
5	Star Inc.	Star Inc.	45
6	Cube Inc.	Cube Inc.	20
7	Alliance Co.	ABC Inc.	500
8	Alliance Co.	ABC Inc.	20
9	Alliance Co.	PPM Co.	300
10	Star Inc.	Star Inc.	45

[BA_Pete · Accepted solution]

@Anonymous ,

Ok. So there's two (similar) ways to do this:

1) Create a new column (easy):

Add a new custom column with the following code:

if [End Customer] = "Not Applicable" then [Purchasing channel] else [End Customer]

---OR---

2) Replace N/A values within the existing column (intermediate):

Find a 'Not Applicable' value in your [End Customer] column, right-click and hit Replace Values.

In the dialog, add any text as the Replace With value, then hit OK:

You will see in your formula bar that it has generated this code:

= Table.ReplaceValue(previousStep,"Not Applicable","abc",Replacer.ReplaceText,{"End Customer"})

Now replace the "abc" section with 'each [Purchasing channel]'. It should look like this:

= Table.ReplaceValue(previousStep,"Not Applicable", each [Purchasing channel], Replacer.ReplaceText,{"End Customer"})

This gives the following output:

Pete