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

[vmonu]

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

[v-chuncz-msft]

@vmonu 

Instead of using Pivot Column in Query Editor, you may try Matrix visual.

[vmonu]

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

[Ashish_Mathur]

Hi,

See if my solution here helps - Append data from multiple worksheets of multiple workbooks where each worksheet has a different head....

[vmonu]

@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]

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)