Power Query or Calculated columns

Thanks for all your answers so far, much appreciated!

The reason behind my questioning is that I have been given a model with various tables and some, like "account" are really slow when I need to refresh it (or make a small change, like adding a new column at the beginning of the code, which happens often, since the whole model is still being developed). And I was wondering if the code here is just too bloated --> thus the idea of transferring some parts of the code, like the flag-stuff, to calculated columns.

Here is the code:

let
Source= CommonDataService.Database("ado.crm4.dynamics.com"),
dbo_account = Source{[Schema="dbo", Item="account"]}[Data],
SelectedColumns = {
"customertypecodename",
"industrycodename",
"pre_countryname",
"pre_fachberateryominame",
"pre_objektbetreueryominame",
"overriddencreatedon",
"createdon",
"address1_line1",
"address1_city",
"address1_postalcode",
"name",
"emailaddress1",
"modifiedon",
"accountid",
"pre_customersegmentationcrmlastmodified",
"telephone1",
"lastonholdtime",
"openrevenue_date",
"lastusedincampaign",
"pre_customer_typename",
"pre_last_visit_date",
"opendeals_date",
"address1_composite",
"ownerid",
"owneridname",
"owningbusinessunitname",
"pre_key_infor",
"primarycontactidname",
"statecodename",
"pre_acquisition_statusname",
"websiteurl",
"pre_firmenname",
"pre_discounts_text",
"fax",
"pre_languagename",
"pre_mobile_phone",
"pre_partner_loungename",
"pre_payment_terms_text",
"pre_shipping_terms_text",
"pre_signal_codename",
"pre_vat_number",
"pre_webshop_admin",
"pre_webshop_loginname"
},

DateColumns = {
"createdon",
"overriddencreatedon",
"modifiedon",
"pre_customersegmentationcrmlastmodified",
"lastonholdtime",
"openrevenue_date",
"lastusedincampaign",
"pre_last_visit_date",
"opendeals_date"
},

CountryPairs = {
{"Österreich", "AT"},
{"Austria", "AT"},
{"Deutschland", "DE"},
{"Germany", "DE"},
{"Schweiz", "CH"},
{"Switzerland", "CH"},
{"Italien", "IT"},
{"Italy", "IT"},
{"Frankreich", "FR"},
{"France", "FR"},
{"Belgien", "BE"},
{"Belgium", "BE"},
{"Niederlande", "NL"},
{"Netherlands", "NL"},
{"Polen", "PL"},
{"Poland", "PL"},
{"Tschechien", "CZ"},
{"Czech Republic", "CZ"},
{"Czechia", "CZ"},
{"Ungarn", "HU"},
{"Hungary", "HU"},
{"Slowakei", "SK"},
{"Slovakia", "SK"},
{"Slowenien", "SI"},
{"Slovenia", "SI"},
{"Kroatien", "HR"},
{"Croatia", "HR"},
{"Rumänien", "RO"},
{"Romania", "RO"},
{"Luxemburg", "LU"},
{"Luxembourg", "LU"},
{"Irland", "IE"},
{"Ireland", "IE"},
{"Norwegen", "NO"},
{"Norway", "NO"},
{"Dänemark", "DK"},
{"Denmark", "DK"},
{"Schweden", "SE"},
{"Sweden", "SE"},
{"Groß Britanien", "UK"},
{"Großbritannien", "UK"},
{"Great Britain", "UK"},
{"United Kingdom", "UK"},
{"Liechtenstein", "LI"},
{"Serbien", "RS"},
{"Serbia", "RS"},
{"Bosnien und Herzegowina", "BA"},
{"Bosnia and Herzegovina", "BA"}
},

CountryMap =
Record.FromList(
List.Transform(CountryPairs, each _{1}),
List.Transform(CountryPairs, each _{0})
),

TrimText =
(v as any) as text =>
if v = null then "" else Text.Trim(Text.From(v)),

IsFilled =
(v as any) as logical =>
TrimText(v) <> "",

ToKey =
(v as any) as text =>
Text.Upper(TrimText(v)),

NormalizeName =
(v as any) as text =>
Text.Upper(
Text.Combine(
List.Select(
Text.Split(TrimText(v), " "),
each _ <> ""
),
" "
)
),

NormalizeEmail =
(v as any) as text =>
Text.Lower(TrimText(v)),

NormalizePhone =
(v as any) as text =>
Text.Select(TrimText(v), {"0".."9"}),

CountryCode =
(v as any) as nullable text =>
let
country = TrimText(v)
in
if country = "" then
null
else
Record.FieldOrDefault(CountryMap, country, null),

AddColumns =
(inputTable as table, columnDefinitions as list) as table =>
List.Accumulate(
columnDefinitions,
inputTable,
(state as table, current as list) =>
Table.AddColumn(
state,
current{0},
current{1},
current{2}
)
),

AddDuplicateFlag =
(
inputTable as table,
keyColumn as text,
countColumn as text,
flagColumn as text
) as table =>
let
FilteredKeys =
Table.SelectRows(
inputTable,
each Record.Field(_, keyColumn) <> ""
),

KeyOnly =
Table.SelectColumns(
FilteredKeys,
{keyColumn}
),

DuplicateCounts =
Table.Group(
KeyOnly,
{keyColumn},
{
{
countColumn,
each Table.RowCount(_),
Int64.Type
}
}
),

NestedColumnName = countColumn & "_Nested",

Merged =
Table.NestedJoin(
inputTable,
{keyColumn},
DuplicateCounts,
{keyColumn},
NestedColumnName,
JoinKind.LeftOuter
),

Expanded =
Table.ExpandTableColumn(
Merged,
NestedColumnName,
{countColumn},
{countColumn}
),

WithFlag =
Table.AddColumn(
Expanded,
flagColumn,
each
let
duplicateCount = Record.Field(_, countColumn)
in
duplicateCount <> null and duplicateCount > 1,
type logical
),

RemovedCount =
Table.RemoveColumns(
WithFlag,
{countColumn}
)
in
RemovedCount,

#"Columns removed by Measure Killer" =
Table.SelectColumns(
dbo_account,
SelectedColumns
),

#"Geänderter Typ" =
Table.TransformColumnTypes(
#"Columns removed by Measure Killer",
List.Transform(DateColumns, each {_, type date})
),

#"Flags hinzugefügt" =
AddColumns(
#"Geänderter Typ",
{
{"InforKey_Flag", each IsFilled([pre_key_infor]), type logical},
{"Country_Flag", each IsFilled([pre_countryname]), type logical},
{"City_Flag", each IsFilled([address1_city]), type logical},
{"PLZ_Flag", each IsFilled([address1_postalcode]), type logical},
{"Street_Flag", each IsFilled([address1_line1]), type logical},
{"Relationship_Flag", each IsFilled([customertypecodename]), type logical},
{"Industry_Flag", each IsFilled([industrycodename]), type logical},
{"PrimaryContact_Flag", each IsFilled([primarycontactidname]), type logical},
{
"Fachberater_ist_Besitzer_Flag",
each
let
owner = ToKey([owneridname])
in
owner <> ""
and (
owner = ToKey([pre_fachberateryominame])
or owner = ToKey([pre_objektbetreueryominame])
),
type logical
},
{"Active_Flag", each [statecodename] = "Aktiv", type logical},
{"Firmenkunde_Flag", each ToKey([pre_customer_typename]) = "FIRMENKUNDE", type logical},
{"Has_Email_Flag", each IsFilled([emailaddress1]), type logical},
{
"AcquisitionStatus_Flag",
each IsFilled(
Record.FieldOrDefault(_, "pre_acquisition_status",
Record.FieldOrDefault(_, "pre_acquisition_statusname", null)
)
),
type logical
},
{"DiscountsText_Flag", each IsFilled([pre_discounts_text]), type logical},
{"Language_Flag", each IsFilled([pre_languagename]), type logical},
{"MobilePhone_Flag", each IsFilled([pre_mobile_phone]), type logical},
{"PaymentTerms_Flag", each IsFilled([pre_payment_terms_text]), type logical},
{"ShippingTerms_Flag", each IsFilled([pre_shipping_terms_text]), type logical},
{"VatNumber_Flag", each IsFilled([pre_vat_number]), type logical},
{"OverriddenCreatedOn_Flag", each IsFilled([overriddencreatedon]), type logical}
}
),

#"Laenderkuerzel hinzugefügt" =
Table.AddColumn(
#"Flags hinzugefügt",
"Laenderkuerzel",
each CountryCode([pre_countryname]),
type text
),

#"BU_Laenderkuerzel hinzugefügt" =
Table.AddColumn(
#"Laenderkuerzel hinzugefügt",
"BU_Laenderkuerzel",
each
if [Laenderkuerzel] = "LU" then "BE"
else if [Laenderkuerzel] = "IE" then "UK"
else [Laenderkuerzel],
type text
),

#"Land_OBU_Match_Flag hinzugefügt" =
Table.AddColumn(
#"BU_Laenderkuerzel hinzugefügt",
"Land_OBU_Match_Flag",
each ToKey([BU_Laenderkuerzel]) = ToKey([owningbusinessunitname]),
type logical
),

#"Normalized Account Name hinzugefügt" =
Table.AddColumn(
#"Land_OBU_Match_Flag hinzugefügt",
"Account Name Normalized",
each NormalizeName([name]),
type text
),

#"Duplicate Keys hinzugefügt" =
AddColumns(
#"Normalized Account Name hinzugefügt",
{
{"InforKey_Normalized", each ToKey([pre_key_infor]), type text},
{"Email_Normalized", each NormalizeEmail([emailaddress1]), type text},
{"Phone_Normalized", each NormalizePhone([telephone1]), type text}
}
),

DuplicateBase =
Table.Buffer(
Table.SelectColumns(
#"Duplicate Keys hinzugefügt",
{
"accountid",
"InforKey_Normalized",
"Account Name Normalized",
"Email_Normalized",
"Phone_Normalized"
}
)
),

DuplicateDefinitions = {
{"InforKey_Normalized", "InforKey_Dup_Count", "Duplicate_INFOR_Flag"},
{"Account Name Normalized", "Name_Dup_Count", "Duplicate_AccountName_Flag"},
{"Email_Normalized", "Email_Dup_Count", "Duplicate_Email_Flag"},
{"Phone_Normalized", "Phone_Dup_Count", "Duplicate_Phone_Flag"}
},

DuplicateFlagsWithKeys =
List.Accumulate(
DuplicateDefinitions,
DuplicateBase,
(state as table, current as list) =>
AddDuplicateFlag(
state,
current{0},
current{1},
current{2}
)
),

DuplicateFlagColumns = {
"Duplicate_INFOR_Flag",
"Duplicate_AccountName_Flag",
"Duplicate_Email_Flag",
"Duplicate_Phone_Flag"
},

DuplicateFlagsWithAny =
Table.AddColumn(
DuplicateFlagsWithKeys,
"Duplicate_Any_Flag",
each
[Duplicate_INFOR_Flag]
or [Duplicate_AccountName_Flag]
or [Duplicate_Email_Flag]
or [Duplicate_Phone_Flag],
type logical
),

DuplicateFlags =
Table.SelectColumns(
DuplicateFlagsWithAny,
{"accountid"} & DuplicateFlagColumns & {"Duplicate_Any_Flag"}
),

#"Duplicate Flags gemerged" =
Table.NestedJoin(
#"Duplicate Keys hinzugefügt",
{"accountid"},
DuplicateFlags,
{"accountid"},
"DuplicateFlags",
JoinKind.LeftOuter
),

#"Duplicate Flags erweitert" =
Table.ExpandTableColumn(
#"Duplicate Flags gemerged",
"DuplicateFlags",
DuplicateFlagColumns & {"Duplicate_Any_Flag"},
DuplicateFlagColumns & {"Duplicate_Any_Flag"}
),

ContactSourceColumns =
Table.SelectColumns(
#"account-contact-hilfstabelle",
{"parentcustomerid", "contactid", "Contact_Count"},
MissingField.Ignore
),

ContactStatsGrouped =
Table.Group(
ContactSourceColumns,
{"parentcustomerid"},
{
{
"Contact_Count",
each
let
groupTable = _,

contactIds =
if Table.HasColumns(groupTable, "contactid") then
List.RemoveNulls(Table.Column(groupTable, "contactid"))
else
{},

countValues =
if Table.HasColumns(groupTable, "Contact_Count") then
List.RemoveNulls(
List.Transform(
Table.Column(groupTable, "Contact_Count"),
each try Int64.From(_) otherwise null
)
)
else
{},

result =
if List.Count(contactIds) > 0 then
List.Count(List.Distinct(contactIds))
else if List.Count(countValues) = 0 then
Table.RowCount(groupTable)
else if List.Max(countValues) <= 1 then
List.Sum(countValues)
else
List.Max(countValues)
in
result,
Int64.Type
}
}
),

#"Kontakte gemerged" =
Table.NestedJoin(
#"Duplicate Flags erweitert",
{"accountid"},
ContactStatsGrouped,
{"parentcustomerid"},
"ContactStats",
JoinKind.LeftOuter
),

#"Kontaktanzahl erweitert" =
Table.ExpandTableColumn(
#"Kontakte gemerged",
"ContactStats",
{"Contact_Count"},
{"Contact_Count"}
),

#"Has_Contact_Flag hinzugefügt" =
Table.AddColumn(
#"Kontaktanzahl erweitert",
"Has_Contact_Flag",
each [Contact_Count] <> null and [Contact_Count] > 0,
type logical
)

in
#"Has_Contact_Flag hinzugefügt"