Forum Discussion
Current and potential customers on same map
Anonymous ,
Not knowing much about your data, the approach I would take is this:
- Potential Customer data and Actual Customer data are the same thing - Customer data, so it should be in one table.
- Add a new column at the source or in Power Query called "Customer Type" (or whatever) with the formula in a custom column that is simply ="Actual" - make it a text column.
- Add a new column in the Excel file via Power Query (do it there vs Excel so you don't have to remember to ever do it in Excel again as new rows come in" with the same column name that is the following formula ="Potential" - make it a text column.
- Harmonize any other columns names as necessary between the actual data and the excel file.
- In the actual data, select Append Queries on the home ribbon, and select the Excel file.
- Now, make sure to right-click on the Excel file query itself and make sure "Enable Load" is not checked.
Any columns that exist in the actual data that do not exist in the potential data will be null in the potential records. The reverse is also true - Excel columns will generate null fields in the actual data.
But now you can simply add data and filter on the Actual/Potential, or create slicers, legends, etc.
Community ChampionAnonymous ,
Not knowing much about your data, the approach I would take is this:
- Potential Customer data and Actual Customer data are the same thing - Customer data, so it should be in one table.
- Add a new column at the source or in Power Query called "Customer Type" (or whatever) with the formula in a custom column that is simply ="Actual" - make it a text column.
- Add a new column in the Excel file via Power Query (do it there vs Excel so you don't have to remember to ever do it in Excel again as new rows come in" with the same column name that is the following formula ="Potential" - make it a text column.
- Harmonize any other columns names as necessary between the actual data and the excel file.
- In the actual data, select Append Queries on the home ribbon, and select the Excel file.
- Now, make sure to right-click on the Excel file query itself and make sure "Enable Load" is not checked.
Any columns that exist in the actual data that do not exist in the potential data will be null in the potential records. The reverse is also true - Excel columns will generate null fields in the actual data.
But now you can simply add data and filter on the Actual/Potential, or create slicers, legends, etc.
This is close to what I already did but I didn't do the step of Un-checking enable load. However, this doesn't work. I use the current/potential column as a slicer and It's only finding current. No option now for potential.
So this is weird... I went and looked and discovered this...
Any reason for the slicer not to work even when both options are present in the column?
This was the by far the correct answer. The mistake I made was that I didn't harmonize my year column name because I simply forgot. My year slicer had a blank and guess what? That blank represented my potential customers.
I'll go back and do the steps again and correctly name everything. Thank you for the help. This community is really great.
- edhans
Awesome Anonymous - glad I was able to help out.