Add Column to assign Unique Name (Condition)

[Anonymous]

In the given Table, I need to add a Custom Column in query editor, to have 'Common name' for each 'customer Code', based on the Minimum branch No.

Please advise how this can be achieved in Power Query.

[AnkitBI · Accepted solution]

Hi @Anonymous . I am able to achieve using below. #"Added Custom" is first capturing Name value for minimum branch, then replacing all values with it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgRix6RkpVgdmIgRRMQQKgRSYAzELqlpUBEjqKKIyiokEWMMEROIiDGSkClcKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Code" = _t, Branch = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Code", Int64.Type}, {"Branch", Int64.Type}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Code"}, {{"AllRows", each _, type table [Customer Code=number, Branch=number, Name=text]}, {"MinimumBranch", each List.Min([Branch]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let minbranch = [MinimumBranch] in 
Table.ReplaceValue([AllRows],each [Name],Table.SelectRows([AllRows],each [Branch] = minbranch){0}[Name],Replacer.ReplaceText,{"Name"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows", "MinimumBranch"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Branch", "Name"}, {"Branch", "Name"})
in
    #"Expanded Custom"

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[AnkitBI]

Hi - Are you trying to extract first 3 characters, then you can use Extract --> First Characters from Add Column Tab.

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[Anonymous]

Hi ,

This is sample data for understanding concept, actual Names will be of varying length.

Since names are inconsitent across Branches, i need to have a consitent Names across customer codes based on the name vailable minimum branch number.

[AnkitBI · Accepted solution]

Hi @Anonymous . I am able to achieve using below. #"Added Custom" is first capturing Name value for minimum branch, then replacing all values with it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgRix6RkpVgdmIgRRMQQKgRSYAzELqlpUBEjqKKIyiokEWMMEROIiDGSkClcKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Code" = _t, Branch = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Code", Int64.Type}, {"Branch", Int64.Type}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Code"}, {{"AllRows", each _, type table [Customer Code=number, Branch=number, Name=text]}, {"MinimumBranch", each List.Min([Branch]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let minbranch = [MinimumBranch] in 
Table.ReplaceValue([AllRows],each [Name],Table.SelectRows([AllRows],each [Branch] = minbranch){0}[Name],Replacer.ReplaceText,{"Name"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows", "MinimumBranch"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Branch", "Name"}, {"Branch", "Name"})
in
    #"Expanded Custom"

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[Anonymous]

Hi Ankit,

Youre awesome, this is what i needed. 

one more additional request is to retian the Original Customer Name also in the Table.

Can you please help on this aswell.

Thanks

[AnkitBI · Accepted solution]

@Anonymous  Use below. Created a Duplicate Column(#"Duplicated Column") for this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRMgRix6RkpVgdmIgRRMQQKgRSYAzELqlpUBEjqKKIyiokEWMMEROIiDGSkClcKBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Code" = _t, Branch = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Code", Int64.Type}, {"Branch", Int64.Type}, {"Name", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Name", "Common Name"),
    #"Grouped Rows" = Table.Group(#"Duplicated Column", {"Customer Code"}, {{"AllRows", each _, type table [Customer Code=number, Branch=number, Name=text,Common Name=text]}, {"MinimumBranch", each List.Min([Branch]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let minbranch = [MinimumBranch] in 
Table.ReplaceValue([AllRows],each [Common Name],Table.SelectRows([AllRows],each [Branch] = minbranch){0}[Common Name],Replacer.ReplaceText,{"Common Name"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows", "MinimumBranch"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Branch", "Name", "Common Name"}, {"Branch", "Name", "Common Name"})
in
    #"Expanded Custom"

[Anonymous]

Thanks a lot