Grouping Columns

[cheid]

I have data that is being imported via an API connection.  There are no measures, just data that I pull in to make the table below minus the headers containing the dates.  In power bi I would like to group all the columnsso that I can create headers (Current, Next, and Future + date) as shown in the screenshot.  Is there anyway to replicate the header (current, next, and future) in power bi matrix or table by grouping or creating a dynamic measure for each column?

[v-tejrama]

Hi @cheid ,

Thank you @HarishKM and @DanieleUgoCopp  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you.

[v-tejrama]

Hi @cheid ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[DanieleUgoCopp]

If the data already comes in with 10 fixed columns and you don’t want to reshape it, then the important thing to know is that Power BI can’t literally “group” columns the way Excel does. That feature just doesn’t exist for a normal table.

What you can do instead is use a Matrix visual. A matrix allows you to fake grouped headers by using a column hierarchy. You create a small helper field (for example with Enter Data or a calculated column) that says which group each column belongs to, like Current, Next, or Future. Then you place that grouping field above the actual column field in the Columns area of the matrix.

Visually, this gives you the effect of grouped headers even though the underlying columns are still separate. It only works in a Matrix, not in a Table, and it’s more about presentation than real column grouping.

[HarishKM]

@cheid Hey,
Best practice recommeded by me as below.

Best-practice (recommended)

• Unpivot your date columns to a tidy “long” table

- Power Query: select all date columns → Transform → Unpivot Columns
- Result: [Entity…], [Date], [Value]

• Add a Date table, relate it to the unpivoted [Date]
• Create a bucket (Current/Next/Future) driven by an “as-of” date

- Optional slicer table AsOf[Date] (single select; default TODAY())
- Bucket (calculated column in Date table):
Bucket =
VAR asof = COALESCE(SELECTEDVALUE(AsOf[Date]), TODAY())
RETURN
SWITCH(TRUE(),
'Date'[Date] = asof, "Current",
'Date'[Date] = EDATE(asof, 1), "Next",
'Date'[Date] > EDATE(asof, 1), "Future",
"Past"
)

• Matrix setup

- Rows: your entity (e.g., Customer/Product)
- Columns: Bucket (outer) → Date (inner)
- Values: SUM(Value)
- This gives grouped headers: Current | Next | Future, with the corresponding dates under each

Alternative (keep wide table, no unpivot)

• Create three measures using an AsOf slicer:

- Current = CALCULATE([Value], 'Date'[Date] = Selected AsOf)
- Next = CALCULATE([Value], 'Date'[Date] = EDATE(Selected AsOf, 1))
- Future = CALCULATE([Value], 'Date'[Date] > EDATE(Selected AsOf, 1))

• Use Field Parameters to place these measures as Matrix columns
• For dynamic header text like “Current (dd-MMM-yy)”, use a Calculation Group (Tabular Editor) to set display names based on the AsOf date

Notes

• Unpivoting is the most scalable and makes dynamic column groups trivial.
• Define “Next/Future” to match your period logic (day/week/month); replace EDATE with DATEADD for weeks/days.

Thanks

Haish K 

If I resolve your issue. Kindly give kudos to this post and accept it as a solution so other can refer this.

[cengizhanarslan]

In native Power BI visuals, a Table can’t do grouped/merged headers like that. A Matrix can, but only if you model the data so the “header group” is a column hierarchy, not separate physical columns. But you can always make some manual touches to your UX design. Just disable auto-width of your column headers, then Insert some Shapes on the top of your matrix.

[danextian]

Hi @cheid 

Measures alone will not be enough as they will be broken down into whatever dimension added to  a matrix viz. For example, Net Points will appear in all of the period categories instead of next and future only.  Also given that you have the same second level headers for Next and Future, how do you identify which  ones belong to which period?

Also, please provide a workable sample data (not an image) and not just the expected result. Of course, the expected result should match the sample data.

[amitchandak · Accepted solution]

@cheid , with Calculation group you can get static-> Current Period, Past Period and Future period 

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0