Forum Discussion

RandyPBI-BH's avatar
RandyPBI-BH
New Member
3 years ago
Solved

Unpivot only selected columns - Dymanic Names

Hello,

 

I have Financial P&L Matrix,  human interface friendly where Rows are the accounts , columns names are actual/(monthyear) or Forecast/(monthyear), and the values in cell are the finacial values. database is something like this:

countryAccountActual/Jan2023Actual/Feb2023Forecast/Mar2023Forecast/Apr2024Forecast/May2025Forecast/Jun2026
United StatesTotal Revenue100010501000105010001050
United StatesTotal Cost500600500600500600
FranceTotal Revenue100011001000110010001100
FranceTotal Cost700800700800700800

 

I do unpivot Only selected columns from the functions and the DAX formula goes like this:

 

Table.Unpivot(#"Expanded romeveheader1", {"Actual/Jan2023", "Actual/Feb2023", "Forecast/Mar2023", "Forecast/Apr2023", "Forecast/May2023", "Forecast/Jun2023"},"Attribute","Value")

 

Question/challenge:

 

At the beggining of April 2023 when the account results are available, the column "Forecast/Mar2023" name is going to change to "Actual/Mar2023"

 

so instead of coming to powerquery and change the column name every month, is there a way that I can unpivot with dymanic column names?

 

Thanks in advance

 

Need Help
  • jgordon11's avatar
    jgordon11
    3 years ago

    Assuming romeveheader1 is not a typo (although removeheader1 seems more likely) and the first two columns are the only ones not unpivoting:

     

    Table.Unpivot(#"Expanded romeveheader1", List.Skip(Table.ColumnNames(#"Expanded romeveheader1"),2),"Attribute","Value")

2 Replies

  • jgordon11's avatar
    jgordon11
    Resolver II
    3 years ago

    Assuming romeveheader1 is not a typo (although removeheader1 seems more likely) and the first two columns are the only ones not unpivoting:

     

    Table.Unpivot(#"Expanded romeveheader1", List.Skip(Table.ColumnNames(#"Expanded romeveheader1"),2),"Attribute","Value")