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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Split and sum the distinct Values

Hi,

 

I have a dataset as below:

 

NameValueTypeTier
Sarath2048
2048
256
Type2
Type2
Type2
Tier1
Tier2
Tier1
Kumar1024
1024
1024
300
Type1
Type1
Type1
Type1
Tier1
Tier1
Tier2
Tier2

 

I need to split the Type and Tier and then count the distinct Values.

 

Kindly help.

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

I happened to know how to achieve this!!

1. Create the table as your sample:

Capture.PNG

2. Transpose the table:

0.PNG

3. Split the column by delimiter:

1.PNG2.PNG

4. Transpose back the table:

3.PNG

5,Fill down the first column and change the datatype of column2

4.PNG

6. Group the rows:

5.PNG

 

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

View solution in original post

8 REPLIES 8
natabird3
Continued Contributor
Continued Contributor

1.JPG2.JPG

 

I am not sure i understand correctly what you try to do. But if you are trying to split the number from the text in a separate column from type and tier, you can go to edit query and select the option (right click on type column): split column by position. Select 0, 4 and after select ok you should get the desired result number separated from column. To get the count simply add in a visual and in the formatting option select the count. Hope this is what you were after if not please clarify a bit.

Anonymous
Not applicable

I want to count the Values based on the Tier.

My data should show like below:

 

Sarath Type2 Tier1 2304
Sarath Type2 Tier2 2048

 

Kumar Type1 Tier1 2048
Kumar Type1 Tier2 1324

 

My problem here is as I am having all the details in one cell, if I split the cell into rows, my row size increases and hence the count is not happening.

 

Is there any way to acheive this??

natabird3
Continued Contributor
Continued Contributor

Actually if you only want to show the value based on the tier you can use a filter. If you add tiers into the filter and select which tier you would like to be displayed only the values for that tier will be displayed in the other visual, being it table or chart. Try that and let me know if you get the desired result.

Hi @Anonymous 

 

Let me know if you'd like to get below result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4sSizJUNJRMjIwsQBSIZUFqUYgOjO1yFApVge/CiM0FaZmWI3wLs1NLAKKGBoYmUAVGFJNgRGKAmMDA0z5WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t, Type = _t, Tier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Value", Int64.Type}, {"Type", type text}, {"Tier", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"Name", "Type", "Tier", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Name", "Type", "Tier"}, {{"Count", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

3.PNG

You can use the "Group BY" in power query, here's a similar post for your reference:

https://community.powerbi.com/t5/Desktop/SUM-rows/td-p/23945

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Anonymous
Not applicable

Group function will work if the Type/Tier are in seperate rows. But in this case all the Type/Tier are in one single cell like below: I mentioned the Row1 and Row 2.

 

In Row 1 I am having 3 Values, 3 Types and 3 Tier--------- likewise in Row2, 4 Values, 4 Types, 4 Tier. Here i mean the count of entries on Rows.

 

NameValueTypeTierHeader
Sarath2048
2048
256
Type2
Type2
Type2
Tier1
Tier2
Tier1
Row1
Kumar1024
1024
1024
300
Type1
Type1
Type1
Type1
Tier1
Tier1
Tier2
Tier2
Row1

 

If I split the cell by rows and then group and sum the values, it gives me incorrect sum.

 

@v-diye-msft @natabird3 @Ashish_Mathur 

Hi @Anonymous 

 

I happened to know how to achieve this!!

1. Create the table as your sample:

Capture.PNG

2. Transpose the table:

0.PNG

3. Split the column by delimiter:

1.PNG2.PNG

4. Transpose back the table:

3.PNG

5,Fill down the first column and change the datatype of column2

4.PNG

6. Group the rows:

5.PNG

 

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

Hi,

You should be able to split data into rows in the Query Editor.  Once that is done, you may use this measure

=DISTINCTCOUNT(Data[Value])

I could not try this on your pasted data because the moment I paste your data in an Excel workbook, the data automatically splits int rows.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

I am not clear.  Is that data in 4 rows?  Clearly show the input Tables and the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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