Add column based on organization name

[Anonymous]

Dear Community,

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-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

[dharmendars007]

Hello @Anonymous , 

Thank you for your question..

Please use the below condition in Power query to get the desired result..

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Org Column", each if
Value.Is([#"Account-Org"],type text) then [#"Account-Org"] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Org Column"}),
#"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Account-Org", type any}, {"Amount", type text}, {"Org Column", type text}})
in
#"Changed Type"

If you find this helpful , please mark it as solution and remember to give kudos
  

Thank You

Dharmendar S

LinkedIN 

[Ahmedx · Accepted solution]

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"