Forum Discussion
append data from two columns only for distinct count.
Hi,
So I have a table of about 25 columns, 35K rows of data.
I want to take two of those columns, append the data to make once column of about 70K of data. Then, remove duplicates.
Would this need to be a new table? If I create a calculated column, I'm going to end up with 70K rows of data, where 35K of them have null value in the other 24 columns columns. Unless there is a programmable way to just do a distinct count of all those rows of data. The possibility of the data that I'm trying to manage is about 50K different possibilities, so I can't just search for a key word or anything.
If I did it in excel I would do exactly that - copy / paste all the data from one column onto the bottom of the other column, then click remove duplicates.
I've searched, but the other options/questions I've found are people trying to do distinct count where they can concatenate the columns of data into a new column with the same # of rows as the original table. This is different.
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.
19 Replies
Not sure if this will help, but when I want to stack two columns of a table into one, I move the two columns to the end (far right) and then simply unpivot the two columns. This leaves you with an "Attribute" column where the attributes are the two column headers and a "Value" column which is the stacked column of data.
I 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.
The 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.
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.
