Forum Discussion

Ahmed_Khalil's avatar
Ahmed_Khalil
Frequent Visitor
2 years ago
Solved

Help to Manage Duplicate Values in Power Query at Power BI

i have the first column and i want a formula or any other way to return the value of Column B to be like below ColumnA ColumnB 1000 1000A 1000 1000B 1000 1000C 1000 1000D 1001...
Need Help
Show and Tell
Tips and Tricks
  • tharunkumarRTK's avatar
    tharunkumarRTK
    2 years ago

      Ahmed_Khalil

    I have written the M-Code for your requirement. 

    Please find the excel file and you will find the m code with in the query 'Table1'

     

     

    Need a Power BI Consultation? Hire me on Upwork

     

    Connect on LinkedIn

     




    Did I answer your question? Mark my post as a solution!
    If I helped you, click on the Thumbs Up to give Kudos.

    Proud to be a Super User!

     

     

  • ronrsnfld's avatar
    ronrsnfld
    2 years ago

    Given:

     

    Using this M Code (which will reproduce the above table:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFCK1SHEMMRgGMEYxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ColumnA"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "add Alpha", each List.FirstN({"A".."Z"},[Count]), type {text}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded add Alpha" = Table.ExpandListColumn(#"Removed Columns", "add Alpha"),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded add Alpha", "ColumnB", each Text.Combine({[ColumnA], [add Alpha]}, ""), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"add Alpha"})
in
    #"Removed Columns1"

    will return:

     

     

sroy_16's avatar
sroy_16
Resolver II
2 years ago

Hello,

Please try using the m.language and see if it works or not.
A new custom column needs to be created and on it have this mcode.

Text.From([ColumnA]) & Text.From(Character.FromNumber(65 + ([Index] - 1) mod 26))

If you need more than 26 combinations (e.g., AA, AB), adjust the formula to handle multiple letters:

let
baseValue = Text.From([ColumnA]),
index = [Index] - 1,
firstLetter = Character.FromNumber(65 + Number.IntegerDivide(index, 26)),
secondLetter = Character.FromNumber(65 + Number.Mod(index, 26))
in
baseValue & firstLetter & secondLetter


Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.