Help to Manage Duplicate Values in Power Query at Power BI

[Ahmed_Khalil]

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	1001A
1001	1001B
1002	1002A
1003	1003A

[tharunkumarRTK · Accepted solution]

  @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 · Accepted solution]

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:

[ahadkarimi]

Hi,
= Table.AddColumn(PreviousStepName, "ColumnB", each Text.From([ColumnA]) & Text.From([Index]))

[sroy_16]

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.

[tharunkumarRTK · Accepted solution]

  @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!