Consolidating Helper Column into Single Column with Nested Functions

[Syndicate_Admin]

I have one column that lists the Group Name Description if the Account is grouped, if the account is not grouped it returns a blank text value (not best practices but can't change the data source) 

For the current reports I just have a logical helper column to see if the data is blank or not, then an additional identifier column to pull data based on the helper.

I've reached two dead ends with the code snippets below. I know I'm missing some sort of identifier but I was not able to find it through searching. Any help in the right direction would be great.

This one returns a null value for Yes

let
    Source = Excel.Workbook(File.Contents("C:\User\Downloads\1a Recon Status Summary.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Account", Int64.Type}, {"Active Certification Status", type text}, {"Approver Name", type text}, {"Preparer Name", type text}, {"Key Account", type text}, {"Financial ID", type text}, {"Profit Center", type any}, {"Cost Center", type any}, {"Company Code", type text}, {"Account Description", type text}, {"Reconciliation Frequency", type text}, {"Account Assignment Type", type text}, {"# of Accounts (Grp)", Int64.Type}, {"Reconciliation Document Count", Int64.Type}, {"Subledger Balance - Imported", type text}, {"GL Balance Account", type number}, {"Consolidation Balance Account", type number}, {"Unidentified Difference Account", type number}, {"Unidentified Difference % Account", type number}, {"Purpose", type text}, {"Period Name", type text}, {"Group - Account Description", type text}}),
    Custom1 = Table.AddColumn(#"Changed Type", "Financial Statement", each if [Account] >= 500000 then "P&L" else "Balance Sheet"),
    IsGroupAccount = (value) =>
     let
        input = each [#"Group - Account Description"],
        output = if input = "" then "No" else "Yes"
    in
    output,

Custom2 = Table.AddColumn(Custom1, "Recon", each if IsGroupAccount = "No" then Text.From([Account])& "." & Text.From([Profit Center]) else [#"Group - Account Description"])
in
 Custom2

This one also returns blank value for Yes

let
    Source = Excel.Workbook(File.Contents("C:\User\Downloads\1a Recon Status Summary.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Account", Int64.Type}, {"Active Certification Status", type text}, {"Approver Name", type text}, {"Preparer Name", type text}, {"Key Account", type text}, {"Financial ID", type text}, {"Profit Center", type any}, {"Cost Center", type any}, {"Company Code", type text}, {"Account Description", type text}, {"Reconciliation Frequency", type text}, {"Account Assignment Type", type text}, {"# of Accounts (Grp)", Int64.Type}, {"Reconciliation Document Count", Int64.Type}, {"Subledger Balance - Imported", type text}, {"GL Balance Account", type number}, {"Consolidation Balance Account", type number}, {"Unidentified Difference Account", type number}, {"Unidentified Difference % Account", type number}, {"Purpose", type text}, {"Period Name", type text}, {"Group - Account Description", type text}}),
   //inserts Financial Statement column based on Account column value  
    Custom1 = Table.AddColumn(#"Changed Type", "Financial Statement", each if [Account] >= 500000 then "P&L" else "Balance Sheet"),
//inserts IsGroupAccount helper column based on if there is a value in the in the Group - Account Description column
    input = each if ([#"Group - Account Description"]) = "" then "No" else "Yes",
    output = Table.AddColumn(Custom1, "Recon", each if input = "No" then (Text.From([Account])& "." & Text.From([Profit Center])) else [#"Group - Account Description"])
in
output

[Syndicate_Admin]

Thanks, as soon as I wrote the reply I saw the updated logic. Thanks again for helping to simplify it.

[Syndicate_Admin]

101 would be Example

103 would be 100003.103 

If Group Account Description is blank then return Account.Profit Center else return the value in Group Account Description

[Vijay_A_Verma · Accepted solution]

Use below formula in a custom column

= if [#"Group - Account Description"]="" then Text.From([Account])&"."&Text.From([Profit Center]) else [#"Group - Account Description"]

[Syndicate_Admin]

Nope wont be able to share that but here is a sample data set just using 15 lines

Account	Profit Center	Group - Account Description
100001	101	Example
100002	102	Example
100003	103
100004	104	Example
100005	105	Example
100006	106	Example
100007	107
100008	108
100009	109
100010	110
100011	111	Example
100012	112	Example
100013	113	Example
100014	114

[Vijay_A_Verma]

I went through your data and code. What is the result expected say for Profit center 101 and 103?

[Vijay_A_Verma]

Can you share some sample data used in file 1a Recon Status Summary.xlsx? Please upload the file without confidential/sensitive data to a cloud storage service such as Onedrive/Google Drive/Dropbox/Box (Onedrive preferred) and share the link here.