Power Query or Calculated columns

[cn4422]

Hi,

I have a an account-table with numerous columns and I now need to creat flag-columens (logic true/false) --> I'm not sure if it's better to create them directly with m-code or add them as calculated columns after the model has been loaded.

Second case is about country-mapping --> I have a country column and need to create another column with mapping values.

Thx for your help!

[cn4422]

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"

[v-menakakota · Accepted solution]

Hi,

Thank you for contacting the Microsoft Fabric community forum.

Thanks for sharing the full query, this gives much better context on the refresh behavior. Based on the code shared, moving the flag columns from Power Query to DAX calculated columns would likely not provide significant benefit, since most of the flags are simple row-level checks that are generally suitable for Power Query transformations during the ETL stage.

The query also contains multiple `Table.Group`, `Table.NestedJoin`, normalization, and buffering operations. Microsoft documentation notes that transformations, joins, grouping operations, and query folding behavior can affect refresh performance depending on the source and transformation sequence.

Your current country mapping approach using `Record.FieldOrDefault` is also a valid approach for smaller mapping lists.

Please refer the below documents

Understanding Query Evaluation and Query Folding in Power Query - Power Query | Microsoft Learn

Query folding indicators in Power Query - Power Query | Microsoft Learn

Use Calculation Options in Power BI Desktop - Power BI | Microsoft Learn

If I’ve misunderstood your needs or if you still encounter issues, please let us know.

Best Regards, 
Community Support Team

[cengizhanarslan]

Calculated columns are computed by the VertiPaq engine after data is loaded into memory. They consume model RAM, cannot be folded back to the source, and are recalculated on every refresh. Power Query transformations, on the other hand, happen during the ETL phase, benefit from query folding (if your source supports it), and result in a smaller, more efficient model.

[trivedisunita]

Hi @cn4422 ,

For the first case If the condition is row‑level and static like ([Status] = "Active"), add it in Power Query (M). Keeps the model lean and refresh‑ready andIf the condition depends on  relationships, use a DAX calculated column. 

and for scond case-

keep a mapping table and join it in Power Query (Merge Queries). This ensures consistency and refresh stability.

Thanks

[danextian]

Ideally, you'll want it done at the  source, next Power Query and then DAX. That is the general rule.  For calculations that involve the same row, do it in Power Query. For calculations that involve scanning a table/multiple rows, DAX would be faster.

[ryan_mayu]

@cn4422 

if it's pure row logic, you can implement in Power Query. However, the speed may slower than DAX.

If you need relationships / lookup logic, then you have to use DAX. However, this column can't be found in Power Query, that means you can not re-use it in PQ.

You need to choose the proper solution based on your real situation. 

[Ashish_Mathur]

Hi,

For the second case, you may create a Many to One relationship as well.

[ExcelMonke]

Hello,

The general rule of thumb is to move steps as far upstream as possible in the ETL. So, if you are able to add the true/false column in your transform steps (i.e. m code portion), that would be the recommendation. 

For your country question, could you elaborate on what you mean by "mapping values"?