Data Extraction

[AnamK]

I have a list of projects and the ones taht are capitalized have the code A followed by a numbers, e.g. A32678 Whereas the ones that are not capitalized are just text string.IS there any way to show on the report Number of projects capitalized vs the ones not capitalized. And then sum of cost of the ones capitalized vs the one that aren't?

PS: I am fairly new to Power BI and have very limited knowledge of scripting

[HammadAkram]

Hi @AnamK, Yes you can achieve this by creating a conditional column using power query editor. And after that just apply the filter to show only Capital onces and similarly for the non capital ones.

Best,

Hammad

[Kedar_Pande]

Hi @AnamK 

Create a Calculated Column:

CapitalizedFlag = IF(LEFT(Projects[ProjectCode], 1) = "A", "Capitalized", "Non-Capitalized")

New Measure

CountProjects = COUNTROWS(Projects)

You can now add a visual (e.g., a bar chart) and place the CapitalizedFlag in the "Axis" and CountProjects in the "Values" field to show the count of capitalized vs. non-capitalized projects.

Create a Measure for Summing the Costs

SumCost = SUM(Projects[Cost])

Add a similar visual, using CapitalizedFlag on the "Axis" and SumCost in the "Values" field, to show the total costs of capitalized vs. non-capitalized projects.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[rajendraongole1]

Hi @AnamK - I have attached pbix file for your reference, as per the given scenerio you will be able to easily see the breakdown of the number of capitalized vs non-capitalized projects and their associated costs

Please check and i hope it works.

[TomMartens · Accepted solution]

Hey @AnamK ,

first: welcome to this community.

Then ...

You can create a Custom column using Power Query that checks if the first character of the Project column is capitalized or not:

This is the code to create the custom column:

if Text.Middle([Projecct] , 0 , 1) = Text.Upper(Text.Middle([Projecct] , 0 , 1))  then "is cpaitalized" else "not capitalized"

This is how the table looks like with the custom column "Capitalized:"

Then, you can create a simple table visual on the report canvas like so:

There is one crucial aspect, though, that you must have in mind: The semantic model is not case-sensitive; this means the semantic model (or, being precise, the vertipaq engine) "understands" both projects "A123456" and "a123456" are being the same. This can be checked if you create a DAX measure that counts the distinct values of column "Project" using the below DAX statement to create a measure:

# of distinct projects = 
DISTINCTCOUNT( 'Table'[Projecct] )

And how it looks like when added to the Card visual: instead of showing the value 4, the measure returns the value 3:

Hopefully, this will provide what you are looking for and help you tackle your challenge.

Regards,

Tom

[zenisekd]

Hi,

I would look for a power query function, that would create a new column, which would somehow highlight if the row has capitalized letters. Then in Power PI I would use a simple condition if this column is blank, then it is not capitalized. 

Solutions for it could be:
https://community.fabric.microsoft.com/t5/Power-Query/HOW-TO-EXTRACT-UPPERCASE-CHARACTERS-FROM-A-COL...
or
https://www.reddit.com/r/excel/comments/p67lv1/power_query_check_if_all_letters_in_a_row_are_in/

Kudos and mark as solution appreciated.