Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have an excel file that has 108 code columns like A6A** and A6K**. I would like to count rows for every single values in excel. However, when I do it with Power BI Pivot Table, it automatically includes some column results to other column. Like below; A6A** column results are under A6K** columns.
Is there any way to do it for independent family results like below?I want to show each column value consecutively for each 108 columns.
Solved! Go to Solution.
Ok. Then... In Power Query M:
let
Źródło = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc6xCoYwDATgd+nsYlrdU3pm+KekhQzF938NSxX5cQg33MeR3gNc2zyuTjGtYQm8/5hn8shz6UHh5jCyWmQYelC+UcaNxBQmQFO0FLf1o/JUUqoMIJEobjgQ04eVZ+zw0bqaOuhFf2+dFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Long VIN" = _t, A6K = _t, A6A = _t]),
#"Unpivot Other Columns" = Table.UnpivotOtherColumns(Źródło, {"Long VIN"}, "Family", "Value")
in
#"Unpivot Other Columns"
Create New Measure:
Count of VIN = DISTINCTCOUNT('Sample'[Long VIN])
Proud to be a Super User!
The sample data like below. Correct, one long VIN value has 108 code families like A6K and A6A.
Long VIN | A6K | A6A |
EWQTEWQTEASW2341 | A6KAA | A6AAA |
QEWRWER2RSDG3412 | A6KAB | A6ABE |
QGRQERGEETQET4351 | A6KAB | A6ABB |
GDSGTQEG32235EFE34 | A6KAB | A6ABD |
QGFWEFEWQRQWE234 | A6KAA | A6AAA |
So based on this sample data that you have here:
Long VIN | A6K | A6A |
EWQTEWQTEASW2341 | A6KAA | A6AAA |
QEWRWER2RSDG3412 | A6KAB | A6ABE |
QGRQERGEETQET4351 | A6KAB | A6ABB |
GDSGTQEG32235EFE34 | A6KAB | A6ABD |
QGFWEFEWQRQWE234 | A6KAA | A6AAA |
What do you want to count? Based on this 5 rows?
Proud to be a Super User!
I want to count specific values for family columns. For example, when I want to get the counts for A6KAB from A6K column and A6AAA from A6A column, the table should be like below. I don't want to get results only for both A6KAB and A6AAA values. I would like to count both values seperately.
Ok. Then... In Power Query M:
let
Źródło = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc6xCoYwDATgd+nsYlrdU3pm+KekhQzF938NSxX5cQg33MeR3gNc2zyuTjGtYQm8/5hn8shz6UHh5jCyWmQYelC+UcaNxBQmQFO0FLf1o/JUUqoMIJEobjgQ04eVZ+zw0bqaOuhFf2+dFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Long VIN" = _t, A6K = _t, A6A = _t]),
#"Unpivot Other Columns" = Table.UnpivotOtherColumns(Źródło, {"Long VIN"}, "Family", "Value")
in
#"Unpivot Other Columns"
Create New Measure:
Count of VIN = DISTINCTCOUNT('Sample'[Long VIN])
Proud to be a Super User!
Can you post sample data? I think the issue here is that have 2 columns in your matrix: columnA with value A6KAH and a columnB with value A6AAA. Then using a count of Long VIN you are receiving this combination in matrix. 🙂
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |