Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a dataset as below:
Name | Value | Type | Tier |
Sarath | 2048 2048 256 | Type2 Type2 Type2 | Tier1 Tier2 Tier1 |
Kumar | 1024 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.
Solved! Go to Solution.
Hi @Anonymous
I happened to know how to achieve this!!
1. Create the table as your sample:
2. Transpose the table:
3. Split the column by delimiter:
4. Transpose back the table:
5,Fill down the first column and change the datatype of column2
6. Group the rows:
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.
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??
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"
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
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.
Name | Value | Type | Tier | Header |
Sarath | 2048 2048 256 | Type2 Type2 Type2 | Tier1 Tier2 Tier1 | Row1 |
Kumar | 1024 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.
Hi @Anonymous
I happened to know how to achieve this!!
1. Create the table as your sample:
2. Transpose the table:
3. Split the column by delimiter:
4. Transpose back the table:
5,Fill down the first column and change the datatype of column2
6. Group the rows:
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.
Hi,
I am not clear. Is that data in 4 rows? Clearly show the input Tables and the expected result.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |