Forum Discussion
Check column for similar values
Hi, I have a software report and on the report it has different versions on different lines eg:
Microsoft Office OneDrive 21.220.1024.0005
Microsoft Office OneDrive 26.45.308.3
Its a full Software report so all different suppliers.
How do I create a column to tell me that there's duplicates entries
For your reference.
Step 1: I add a 'Software No Version' column below.
Step 2: I replaced Value,"Bit"-->"".
<After>
17 Replies
That doesnt work, it returns everything as false. For info the table is called Softwarescan and the column is called Software Title
For your reference.
Hi so to be clear, the Software has the name and version number so a duplicate is where the title is listed not the title and software version eg
Microsoft Office OneDrive 19.43.304.13
Microsoft Office OneDrive 21.220.1024.0005Im looking for it tell me , as an example where Onedrive is listedtwice so its part of the text thats the same, thank you
If all values for the software include both name and version you could use Power Query to split the column by the right-most delimiter, using space as the delimiter. That would give you a column for the name and a column for the version.
You could create a list of duplicates by creating a measure which is a simple COUNTROWS( 'Table' ), put that into a visual with the software name column and filter it so that it only shows where the value is > 1.
That doesnt work either, where i have titles like this:
Adobe Acrobat DC 26.001.21346
Adobe Acrobat DC 26.001.21346 64-Bit
Column 1 Column 2
Adobe Acrobat DC 26.001.21346
Adobe Acrobat DC 26.001.21346 64-BitYou could try splitting it at the first numeric character, something like
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hctLCoAgEADQqwyuaxhHk7aV2/AA4aKPgpuEks6fdIH27y2LGI68BRj2K29rATsBGySSyFJpI3zzI8Dodkzlg3Oq5s6xgIsx7QHcGeyVngBcNRNKYl0rdX/coO5QUY9KeP8C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), SplitCol = Table.SplitColumn( #"Changed Type", "Column1", Splitter.SplitTextByCharacterTransition( { "A".."Z", "a".."z", " ", "-"}, { "0".."9"}), {"Name", "Version"} ), #"Trimmed Text" = Table.TransformColumns(SplitCol,{{"Name", Text.Trim, type text}}) in #"Trimmed Text"
Hi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
For youre reference.
Step 0:I use these DATA below.
<DATA (2)>
Step 1: I add a column below.
Is Duplicate = IF(CALCULATE(COUNTX(FILTER('DATA (2)','DATA (2)'[Software]=MAX('DATA (2)'[Software])),'DATA (2)'[Software]),ALLEXCEPT('DATA (2)','DATA (2)'[Software]))>1,"Duplicates","Single")
Thank you but this is wrong too. Im not interested in the version number , only the Software Title so if it lists Adobe Acrobat 3 times and they are all different versions I only want the count of the software title?
For your reference.
Step 1: I add a 'Software No Version' column below.
Step 2: I replaced Value,"Bit"-->"".
<After>
Hi dommyw277,
Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to mickey64, Ashish_Mathur, johnt75, parry2k, for those inputs on this thread.Has your issue been resolved? If the response provided by the community member mickey64, Ashish_Mathur, johnt75, parry2k, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.
Thank you for using the Microsoft Community Forum.Hi dommyw277,
Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.Thank you.
