Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Add column based on organization name

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 t...
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    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"
dharmendars007's avatar
dharmendars007
Memorable Member
2 years ago

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