Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
New2PowerBI
Helper III
Helper III

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 1Site 2Site 3Site 4
N01S01  
S01IT1  
N01SE1IT1 
SE1N01S01IT1
IT1S01  

 

What I'd like to see:

N01S01SE1IT1
YesYesNoNo
NoYesNoYes
YesNoYesNo
YesYesYesYes
NoYesNoYes

 

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!

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

View solution in original post

7 REPLIES 7
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

@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?

1.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

@New2PowerBI ,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

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.  

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.