Forum Discussion
Help - Quirky Data
I connected to a table in Excel; it pulls data from SharePoint (I do not have permissions to directly connect to SharePoint).
My issue is; users can select multiple check boxes on the screen. When the data comes across, it does not come very clean, it comes like:
#N01
#S01; SE1; IT1
#S01; IT1
etc.
I changed the data so that it can pull it into columns; Power BI labeled them Site 1, Site 2, Site 3, Site 4. Which was good, at first because it segregated the data, however, my Site 1, Site 2, etc., is not always the same, I can have Site 1 be "#N01", or "#S01. Here is a snapshot of what I see and what I would like to see done...tried the Add Column from Examples (crossed fingers), did not work, too many variables I guess, so I'm stuck. Any suggestions??
What I see:
| Site 1 | Site 2 | Site 3 | Site 4 |
| N01 | S01 | ||
| S01 | IT1 | ||
| N01 | SE1 | IT1 | |
| SE1 | N01 | S01 | IT1 |
| IT1 | S01 |
What I'd like to see:
| N01 | S01 | SE1 | IT1 |
| Yes | Yes | No | No |
| No | Yes | No | Yes |
| Yes | No | Yes | No |
| Yes | Yes | Yes | Yes |
| No | Yes | No | Yes |
My hope is that I can use this to help me further filter the information. Something to note; each line item has additional information, i.e., who entered it, the date, etc. I need to retain that information to each record.
As always, any help will be greatly appreciated!
Want to give you an update; I was able to solve this after I explained to you what I wanted. Essentially, I created four Conditional Columns; I set 4 IF statements to pick up the area (to display a True) and then ELSE False. I did one column at a time and essentially got exactly what I needed. Thanks again; your questions prompted me to think about the problem differently.
7 Replies
New2PowerBI,
Based on the above sample data, I am not sure that how you determine “yes” or “no” for your columns(N01, S01,SE1,IT1). Could you please describe more details about the logic that you use to get expected table?
Regards,
LydiaNot entirely sure how to get there...my goal is to build a visual where I can say, give me everything that impacts "N01", or "S01", etc, like a slicer. The issue I have is that when the data comes thru, it populates in Site 1, Site 2, etc., but not always in the same manner, so I can't assume that all values in Site 1 are "N01", if that makes sense.
My thought was to transform the data from the "Sites" and build a column where if any of the Sites are populated and they equal "N01", then that column is "yes" if not, "no". That way, I can build my visuals based on that data.
Again, I'm a little stumped on this one and willing to try something new to get to end result too. Thanks! Sorry for delay in response, I've been off sick on and off. Thanks!
New2PowerBI,
How do you relate the highlighted values from the second table to the first table? In other works, which logic you use to determine if the highlighted values are yes or no in the second table?
Regards,
Lydia
