Forum Discussion

vmonu's avatar
vmonu
Icon for Helper II rankHelper II
6 years ago

How to expand (or include) new column header dynamically in Power BI

Good day folks,

I have table that has rolling days coming from source.

As a result the header of the table got to change dynamically.

 

Unfortunately PBI is making me selecting the new day for it to show..

 

First day

----------

Example : Jan 1st  Jan 2   Jan 3..... Jan 31

Car                   1          2         3           33

Plane             5        10         20       25

 

Second day

------------

Example :   Jan 2   Jan 3..... Jan 31  Feb 1

Car                  2         3           33    0

Plane              10         20       25     5

 

Here is the Code that produces this output

 

Please note that I use Pivoted column has the data comes for Day :) and i want to show it as a header...

 

let
Source = Sql.Database("xxxxx", "test"),
dbo_tmpView2 = Source{[Schema="dbo",Item="tmpView2"]}[Data],
#"Pivoted Column" = Table.Pivot(dbo_tmpView2, List.Distinct(dbo_tmpView2[Quarter]), "Quarter", "Cnt")

in
#"Pivoted Column"

 

I am thinking i should use ExpandedColumns but it's giving me error saying that the columnname has to be text (meaning hardcode).

 

Thanks

MN

Need Help

5 Replies

    • vmonu's avatar
      vmonu
      Icon for Helper II rankHelper II
      6 years ago

      Ashish_Mathur  , i'M getting error (saying some image64 not valid etc.,) in the 3rd step ...and i am still not sure based on the video and blog that it will work.

       

      You are trying to kind of merge 2 tables into 3rd one. 

       

      I'm trying to just get the data and repopulate the header.

       

      Can you just see/try based on my simple data sets i gave above

       

      TIA

      MN

      • vmonu's avatar
        vmonu
        Icon for Helper II rankHelper II
        6 years ago

        I played with TableExpandTableColumn but i am getting this error

         

        Expression.Error: The column 'Agerange,2017 Q2,2017 Q3,2017 Q4,2018 Q1,2018 Q2,2018 Q3,2018 Q4,2019 Q1,2019 Q2,2019 Q3,2019 Q4,' of the table wasn't found.
        Details:
        Agerange,2017 Q2,2017 Q3,2017 Q4,2018 Q1,2018 Q2,2018 Q3,2018 Q4,2019 Q1,2019 Q2,2019 Q3,2019 Q4,

         

        Here is the row i added 

         

        let
        Source = Sql.Database("sqltest", "S_test"),
        dbo_tmpView2 = Source{[Schema="dbo",Item="tmpView2"]}[Data],
        #"Pivoted Column" = Table.Pivot(dbo_tmpView2, List.Distinct(dbo_tmpView2[Quarter]), "Quarter", "Cnt"),
        #"Expanded Column"=Table.ExpandTableColumn(#"Pivoted Column", Lines.ToText(Table.ColumnNames(#"Pivoted Column"),","),List.Distinct(Table.ToList(#"Pivoted Column")))
        in
        #"Expanded Column"

         

         

        --- This is the extra row i added

        = Table.ExpandTableColumn(#"Pivoted Column", Lines.ToText(Table.ColumnNames(#"Pivoted Column"),","),List.Distinct(Table.ToList(#"Pivoted Column")))

         

        Basically i was trying to show the new data i.e. not selected and showing automaitcally... Am i correct in this.

         

        In other words , i don't want to select the new columns everytime (and that's what i am trying to do)

    • vmonu's avatar
      vmonu
      Icon for Helper II rankHelper II
      6 years ago

      Nope Matrix doesn't cut it... thanks v-chuncz-msft 

       

      All i am trying is simple

       

      Jan   Feb....... Dec2019

       

      Next month or later

       

      Feb Mar .... Dec2019  Jan 2020 etc.,

       

       

      Any other options

       

      Thanks

      MN