Data validation against different datasets

[Anonymous]

Good day!

I have been developing a revit model health dashboard in Power BI, and for my first version of the work I did all of the calculations in excel (see the link below) and loaded the polished data in PBI. What I am trying to do next is to recreate in PBI the calculations I made in Excel. 

For the data validation I am trying to do, the idea is I have a raw data, and I need to determine if every specific cell is appropriately populated with data. If the cell has "-", "N/A", "TBC", or is blank, then it does not comply with the requirement. The columns in the raw data are the parameters that are included in the validation, and the rows are the revit objects and corresponding data that are needed to be checked against. Kindly look at the file i provided through a link below. The calculations I'm trying to do is found in "Data Content" tab, the raw data is found in "All" tab. There is also a tab called "parameter categories", where there is a list of revit categories/objects which are needed to be populated with data per parameter.

The flow of my data validation is as follows:

1. Evaluate each cell with "0" if the cell contains "-", "N/A", "TBC", or is blank, otherwise it is "1".

2. Check if the object is required to be populated with data as per the "Parameter Categories" tab

3. Evaluate the cell with "-" if the specific cell doesn't need to be populated with data as per the "parameter categories" tab, otherwise evaluate the cell with "Y" if the cell is appropriately populated with data or "N" if the specific cell contains -", "N/A", "TBC", or is blank.

I was trying to look for ways to cross reference data across different datasets but it seems it is quite complicated or very difficult to do. I've also tried to look for workarounds but to no avail. So what I wanted to ask is, if the calculations I am trying to do in PBI is doable? If so, what are your tips for me to do it?

I hope you understand my explanation, apologies if I didn't quite capture what I intend to say. I'll try to clarify any questions you may have. Thank you and have a good day!

Link to the file:

https://drive.google.com/open?id=1F5URXfTdGAFmizeUWc8CngTAvkKc_zVr

[TeigeGao]

Hi @Anonymous ,

I would suggest you explain your requirement with your sample data and share the expected result to us for analysis. I think I can't understand your requirement very well.

>>Evaluate each cell with "0" if the cell contains "-", "N/A", "TBC", or is blank, otherwise it is "1".

This requirement is easy, we can do that in Power Query, in power query, we can add a conditional column like below:

Best Regards,

Teige

[Anonymous]

Hi Teige,

I get that there is a calculated column for that. I meant to do the checking for all columns in the raw data. So, in the screenshot below there are cells that are populated with "-" and blanks. These cells should be evaluated with a "0" in a new table as well as cells with "N/A" or "TBC" in it instead of the appropriate data, otherwise the cells should be evaluated with a "1".

I hope this helps.