Forum Discussion
Using First 6 Characters in a Table to Remove Duplicates
Hi Anonymous
Sorry for the late reply. I didn't think of a good method only using DAX at the report side. I think of a method which is a combination of Power Query and DAX. You may have a look at it.
First in Power Query Editor, add two custom columns to extract the numbers and Name length for each row.
let _allNumbers = Text.Select([Name], {"0".."9"}) in if Text.Length(_allNumbers)>= 10 then Text.Start(_allNumbers,10) else Text.Start(_allNumbers,6)Text.Length([Name])After applying the data to the model, add a new column with the following DAX.
Flag = VAR _shortnameLen = CALCULATE(MIN(SourceData[Name Length]),ALLEXCEPT(SourceData,SourceData[Numbers])) RETURN IF(SourceData[Name Length]=_shortnameLen,1,0)You can put this new Flag column on the table visual as a filter field to show values whose Flag is 1.
Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Good morning,
Yes, all the article numbers are unique. Is there a way to extract or distinguish the sources outside of the Power Query editor?
Regarding the last 2 rows, ideally it would be better if all the numbers were extracted.
I have found a way to do this in the PQE, but as it frequently updates, I wanted to see if there was a way to do make those changes without it.
Thank you.
Hi Anonymous
Here is another method only using Power Query. You can download the pbix to see details. The previous method sample is also included in it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9BVcCwqyUzOSTVU0lEKz8zOVIrVgUsiSbnn56fnpCIkzWGSRlgkLWCSxlgkLRF2mqDbaWaAkDRFljQ2sTAESlmagyBCjRluNUgqYC6IBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Source = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Source", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Numbers", each let _allNumbers = Text.Select([Name], {"0".."9"}) in if Text.Length(_allNumbers)>= 10 then Text.Start(_allNumbers,10) else Text.Start(_allNumbers,6)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Name Length", each Text.Length([Name])),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Numbers"}, {{"All Data", each _, type table [Name=nullable text, Source=nullable text, Numbers=text, Name Length=number]}}),
#"Added Custom2" = Table.AddColumn(#"Grouped Rows", "All Data Sort", each Table.Sort([All Data],{"Name Length",Order.Descending})),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "RemoveData", each if Table.RowCount([All Data Sort]) > 1 then Table.RemoveFirstN([All Data Sort],1) else [All Data Sort]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"RemoveData"}),
#"Expanded RemoveData" = Table.ExpandTableColumn(#"Removed Other Columns", "RemoveData", {"Name", "Source", "Numbers", "Name Length"}, {"Name", "Source", "Numbers", "Name Length"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded RemoveData",{"Source", "Name"})
in
#"Removed Other Columns1"
Jing