Help - Quirky Data

[New2PowerBI]

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!

[v-yuezhe-msft]

@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,
Lydia

[New2PowerBI]

Not 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!

[v-yuezhe-msft]

@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

[New2PowerBI]

If any of the Sites, Site1-4, had the value, I would want that value to tie to that new column, i.e., if Site 1 said N01, then I would expect a "True or Yes" value in N01 column.  The hard part, for me, is that the value can exist in any of the four columns, Sites1-4.  Does that help?

[v-yuezhe-msft]

@New2PowerBI ,

Sorry for that I miss the image in my second reply. I edit it, please review the image.

Regards,
Lydia

[New2PowerBI · Accepted solution]

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. 

[New2PowerBI]

I think I understand your question and my reply is the same.

I want Power BI to look at ALL Site1 column values and IF there is a site, let's say, "N01", then I want it to put "Yes" in a new column I create called "N01".

I would want the same logic for the remainder of the sites.  Hope this helps.  Thanks again for your time and patience.