Add column based on organization name

Question

Dear Community,&nbsp;I have an Excel file with with 2 organization names in one column. Need to add column so it puts Org bane against its account.Account-OrgAmountOrganization (need to add this coulumn)XXXXX LLC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;XXXXX LLC1022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;XXXXX LLC1030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 65,979,610XXXXX LLC1210&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 266,002,627XXXXX LLC1254&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 407,468XXXXX LLC1421&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 273,321XXXXX LLCYYYYY LLC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;YYYYY LLC1022&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;YYYYY LLC1030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 386,301,847YYYYY LLC1210&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,426,782,560YYYYY LLC1251&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (1,660,057)YYYYY LLC1254&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;YYYYY LLC7480&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;YYYYY LLC7710&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&nbsp;&nbsp;YYYYY LLC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

ahmedx · Accepted Answer

pls try yjis code in power query
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZJBDsJACEWvQmalyTcBhoHp2m0PoGl6N8/iyXRSXXZsjRshYcX7gQ/TlC4taBzPCel++zbp1GqaMSVh1d9pZe5qkRcMMcCFF0BlDSB1B7PCNV69xTYOSsYB87pwprKV08jIz/bGXVv8odOUqyOzoFqsm0wCU0dURfH3MUrfKDoI3Blc4rj3JB83Cqv939mjFatvtU9rfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account-Org" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account-Org", type text}, {"Amount", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if (try Number.From( [#"Account-Org"]) otherwise "i")="i" then [#"Account-Org"] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
    #"Filled Down"

Forum Discussion

Add column based on organization name

2 Replies

Related content

Comparing multiple Organizations with Base Organization

Sequential Order column based on multiple criteria

Add duplicate row based on column

Matrix column based on other matrix columns

Count base from other column

Account-Org	Amount	Organization (need to add this coulumn)
XXXXX LLC	-	XXXXX LLC
1022	-	XXXXX LLC
1030	65,979,610	XXXXX LLC
1210	266,002,627	XXXXX LLC
1254	407,468	XXXXX LLC
1421	273,321	XXXXX LLC
YYYYY LLC	-	YYYYY LLC
1022	-	YYYYY LLC
1030	386,301,847	YYYYY LLC
1210	1,426,782,560	YYYYY LLC
1251	(1,660,057)	YYYYY LLC
1254	-	YYYYY LLC
7480	-	YYYYY LLC
7710	-	YYYYY LLC