Forum Discussion
Alias on SummarizeColumns
Hi eduardozn ,
Thank you for reaching out to the Microsoft Fabric Community.
As lbendlin correctly mentioned, SUMMARIZECOLUMNS maintains column lineage and does not support direct aliasing.
To achieve your goal removing table names and formatting column names with underscores we recommend wrapping your SUMMARIZECOLUMNS output with SELECTCOLUMNS to manually rename fields.You can also dynamically filter to the current week's Monday using:
CurrentMonday = TODAY() - WEEKDAY(TODAY(), 2) + 1Recommended approach:
VAR __DS0Core = SUMMARIZECOLUMNS(...your fields...) VAR __DS0Renamed = SELECTCOLUMNS( __DS0Core, "AS_OF_DATE", [AS OF DATE], "FINAL_CATEGORY", [FINAL CATEGORY], -- and so on ) EVALUATE __DS0RenamedThis will give you clean, underscore-formatted column names without table prefixes and apply the dynamic Monday filter as required.
I hope this will resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hi eduardozn ,
Thank you for reaching out to the Microsoft Fabric Community.
As lbendlin correctly mentioned, SUMMARIZECOLUMNS maintains column lineage and does not support direct aliasing.
To achieve your goal removing table names and formatting column names with underscores we recommend wrapping your SUMMARIZECOLUMNS output with SELECTCOLUMNS to manually rename fields.
You can also dynamically filter to the current week's Monday using:
CurrentMonday = TODAY() - WEEKDAY(TODAY(), 2) + 1Recommended approach:
VAR __DS0Core = SUMMARIZECOLUMNS(...your fields...)
VAR __DS0Renamed = SELECTCOLUMNS(
__DS0Core,
"AS_OF_DATE", [AS OF DATE],
"FINAL_CATEGORY", [FINAL CATEGORY],
-- and so on
)
EVALUATE __DS0Renamed
This will give you clean, underscore-formatted column names without table prefixes and apply the dynamic Monday filter as required.
I hope this will resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
I managed to make it work like this. I first used SUMMARIZECOLUMNs and then used the SELECTCOLUMNS.
It is confusing to use SUMMARIZECOLUMNS, since in some examples I have seen they add aliases directly to it.
I suppose removing square brackets [] is impossible? Can only be done through power query?
DEFINE
VAR __MaxAsOfDate =
CALCULATE(
MAX('Dmd TPD Snapshot'[AS OF DATE])
)
VAR __DS0FilterTable =
TREATAS({__MaxAsOfDate}, 'Dmd TPD Snapshot'[AS OF DATE])
VAR __DS0Core =
SUMMARIZECOLUMNS(
'Dmd TPD Snapshot'[AS OF DATE],
'Dmd TPD Snapshot'[FINAL CATEGORY],
'Dmd TPD Snapshot'[GROWTH TEAM],
'Dmd TPD Snapshot'[SBU],
'Dmd TPD Snapshot'[BRAND NAME],
'Dmd TPD Snapshot'[STYLE],
'Dmd TPD Snapshot'[COLOR],
'Dmd TPD Snapshot'[SIZE],
'Dmd TPD Snapshot'[CAPACITY GROUP],
'Dmd TPD Snapshot'[MEGA WORKCENTER],
'Dmd TPD Snapshot'[SUPPLY WINDOW],
'Dmd TPD Snapshot'[STOCKING CATEGORY],
'Dmd TPD Snapshot'[LCA CLASSIFICATION],
__DS0FilterTable,
"SUM_ONHAND", CALCULATE(SUM('Dmd TPD Snapshot'[ONHAND])),
"SUM_INTRANSIT", CALCULATE(SUM('Dmd TPD Snapshot'[INTRANSIT])),
"SUM_WIP", CALCULATE(SUM('Dmd TPD Snapshot'[WIP])),
"SUM_FIRM_SUPPLY", CALCULATE(SUM('Dmd TPD Snapshot'[FIRM SUPPLY])),
"SUM_WITHIN_1_LT_DEMAND_STOCKTARGET", CALCULATE(SUM('Dmd TPD Snapshot'[WITHIN 1 LT DEMAND STOCKTARGET])),
"SUM_WITHIN_1_LT_DEMAND_OPENORDER", CALCULATE(SUM('Dmd TPD Snapshot'[WITHIN 1 LT DEMAND OPENORDER])),
"SUM_WITHIN_1_LT_DEMAND_FORECAST", CALCULATE(SUM('Dmd TPD Snapshot'[WITHIN 1 LT DEMAND FORECAST])),
"SUM_CUMULATIVE_LEAD_TIME", CALCULATE(SUM('Dmd TPD Snapshot'[CUMULATIVE LEAD TIME]))
)
VAR __DS0BodyLimited =
TOPN(
500000,
__DS0Core,
'Dmd TPD Snapshot'[AS OF DATE],
1,
'Dmd TPD Snapshot'[FINAL CATEGORY],
1,
'Dmd TPD Snapshot'[GROWTH TEAM],
1,
'Dmd TPD Snapshot'[SBU],
1,
'Dmd TPD Snapshot'[BRAND NAME],
1,
'Dmd TPD Snapshot'[STYLE],
1,
'Dmd TPD Snapshot'[COLOR],
1,
'Dmd TPD Snapshot'[SIZE],
1,
'Dmd TPD Snapshot'[CAPACITY GROUP],
1,
'Dmd TPD Snapshot'[MEGA WORKCENTER],
1,
'Dmd TPD Snapshot'[SUPPLY WINDOW],
1,
'Dmd TPD Snapshot'[STOCKING CATEGORY],
1,
'Dmd TPD Snapshot'[LCA CLASSIFICATION],
1
)
EVALUATE
SELECTCOLUMNS(
__DS0BodyLimited,
"AS_OF_DATE", [AS OF DATE],
"FINAL_CATEGORY", [FINAL CATEGORY],
"GROWTH_TEAM", [GROWTH TEAM],
"SBU", [SBU],
"BRAND_NAME", [BRAND NAME],
"STYLE", [STYLE],
"COLOR", [COLOR],
"SIZE", [SIZE],
"ONHAND", [SUM_ONHAND],
"INTRANSIT", [SUM_INTRANSIT],
"WIP", [SUM_WIP],
"FIRM_SUPPLY", [SUM_FIRM_SUPPLY],
"CURRENT_DEMAND_STOCKTARGET", [SUM_CURRENT_DEMAND_STOCKTARGET],
"CURRENT_DEMAND_OPENORDER", [SUM_CURRENT_DEMAND_OPENORDER],
"CURRENT_DEMAND_FORECAST", [SUM_CURRENT_DEMAND_FORECAST],
"CAPACITY_GROUP", [CAPACITY GROUP],
"MEGA_WORKCENTER", [MEGA WORKCENTER],
"SUPPLY_WINDOW", [SUPPLY WINDOW],
"STOCKING_CATEGORY", [STOCKING CATEGORY],
"LCA_CLASSIFICATION", [LCA CLASSIFICATION],
"CUMULATIVE_LEAD_TIME", [SUM_CUMULATIVE_LEAD_TIME]
)
ORDER BY
[SKU]