Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 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"
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.
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.
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.
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
@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"
Thanks a lot
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.