Forum Discussion
Helper IIIappend data from two columns only for distinct count.
Thank you Imke for your patience and assistance. I was finally able to get it using the old copy/paste/paste/merge/remove duplicates way. I was able to get the chart right too - apparently I had the relationship pointing the wrong way. Once I changed the direction, it is now displaying the right values.
Community ChampionI would copy your query, use an Append Query step to append your original query, use a calculated column in your query to concatenate your columns and then do a remove duplicates on that column. Although, I'm not exactly certain of your use case as it sounds a bit strange, I'm therefore not sure I have the solution correct.
Helper IIIThe thing though is I only need the number of distinct values in those columns.
For example - here is a data analogy
| Name | Data1 | Name2 | Data3 |
| a | 3 | c | 6 |
| a | 4 | c | 7 |
| b | 4 | a | 2 |
| d | 5 | a | 2 |
| c | 6 | a | 2 |
| b | 2 | d | 4 |
| a | 4 | c | 4 |
| d | 5 | d | 6 |
| d | 6 | c | 6 |
| c | 2 | a | 4 |
| a | 1 | b | 5 |
| a | 5 | b | 6 |
| c | 6 | d | 7 |
| d | 7 | d | 5 |
I want to take all the values under Name, and Name2, and count how many unique. (except I'd have more than 4 different options of course, I have potentialy 40K unique values as they are configuration items in a CMDB). An item could appear under Name, or Name2, or could appear multiple times under both.
If I append my data, I'm going to end up with double of the values under Name - but not necessary Name2 under Name. I essentially want to copy the full row of data in Name2 under Name. The rest of the columns don't matter for what I need. I could create two copies of the query, delete all other columns, rename the column so they match in both new queries, then append them, but it seems like there should be a better way.
- ImkeF
You can take this simple formula to do this:
List.Count(List.Distinct(List.Union(Table.ToColumns(Source))))
Where "Source" is the reference to the table to be analyzed.
It reads inside-out as in Excel:
Table.ToColumns: transfers every column of your table to a list and then combines all those lists into a big list (nested list of lists).
List.Union: combines them
List.Distinct: removes dups
List.Count: does what it says ;-)
This is case-sensitive. Pls let me know if you want a case-insensitive count instead.
- Lenihan
Thank you for the reply,
With this being just a number - where would this be created? It doesn't appear to be a new column since just a number, but when I try to create a new measure, it won't let me select any tables as the source, only existing measures I've already created.
I tried doing this formula too, based on a post from your earlier for someone else,
List.Distinct(List.Union([CI Identifier], [Child CI Identifier]))
with CI Identifier and Child CI Identifier being the two columns I'm trying to merge and I was going to just then do a count on them, but I get this error:
Expression.Error: We cannot convert the value "CI00000085018" to type List.
Details:
Value=CI00000085018
Type=TypeIn every row. the CI######## are the values within the rows of data.
- ImkeF
Having difficulties understanding your request.
My formula actually returns just a single value (the number of distinct values in the whole table) and would be loaded as a table with one column and one row to your data model where it could be adressed by any DAX-function.
Do you need the number of distinct values in just some specific columns?
Or sth totally different? If so, pls post a clear picture of input-data and desired output-data.
