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

      • AnkitBI's avatar
        AnkitBI
        Solution Sage
        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"