Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add Column to assign Unique Name (Condition)

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...
Need Help
  • AnkitBI's avatar
    AnkitBI
    6 years ago

    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's avatar
    AnkitBI
    6 years ago

    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"
AnkitBI's avatar
AnkitBI
Solution Sage
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      AnkitBI
      Solution Sage
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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