Forum Discussion
Alias on SummarizeColumns
Hello,
I have an excel table DAX query from a PowerBI semantic model. The main issue I have with this is that the table column names all have 'Dmd TPD Snapshot'[columnName]. My goal is to clean the columns names and leave just 'columnName' and replacing each space between words with an underscore (_)
Example:
Instead of 'Dmd TPD Snapshot'[AS OF DATE] I want AS_OF_DATE.
I have tried adding aliases directly to SUMMARIZECOLUMNS but I get errors like Query(30,13) A single value for column '<oii>LCA CLASSIFICATION</oii>' in table '<oii>Dmd TPD Snapshot</oii'> cannot be determined. This can heppend when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Please help to clean the column names and if possible have the query filter itself dynamically to monday of each current week
DEFINE
VAR __DS0FilterTable =
TREATAS({DATE(2025, 4, 21)}, '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,
"SumONHAND", CALCULATE(SUM('Dmd TPD Snapshot'[ONHAND])),
"SumINTRANSIT", CALCULATE(SUM('Dmd TPD Snapshot'[INTRANSIT])),
"SumWIP", CALCULATE(SUM('Dmd TPD Snapshot'[WIP])),
"SumFIRM_SUPPLY", CALCULATE(SUM('Dmd TPD Snapshot'[FIRM SUPPLY])),
"SumWITHIN_1_LT_DEMAND_STOCKTARGET", CALCULATE(SUM('Dmd TPD Snapshot'[WITHIN 1 LT DEMAND STOCKTARGET])),
"SumWITHIN_1_LT_DEMAND_OPENORDER", CALCULATE(SUM('Dmd TPD Snapshot'[WITHIN 1 LT DEMAND OPENORDER])),
"SumWITHIN_1_LT_DEMAND_FORECAST", CALCULATE(SUM('Dmd TPD Snapshot'[WITHIN 1 LT DEMAND FORECAST])),
"SumCUMULATIVE_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'[SKU],
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
__DS0BodyLimited
ORDER BY
'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]
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.
3 Replies
- lbendlin
Super User
That's how SUMMARIZECOLUMNS works. If you don't like that, use SUMMARIZE instead, break the lineage, or encapsulate your code in SELECTCOLUMNS.
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.
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]
