Forum Discussion
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 | 1001A |
| 1001 | 1001B |
| 1002 | 1002A |
| 1003 | 1003A |
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!
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:
4 Replies
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!
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:
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.Hi,
= Table.AddColumn(PreviousStepName, "ColumnB", each Text.From([ColumnA]) & Text.From([Index]))
