cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Data_Me_Up Frequent Visitor
Frequent Visitor

Source Data Column Header Changes each month

Hi,

 

This is my first post joining this community, appreciate any feedback.

 

My dataset is a financial report showing actuals and forecast. July - June.

 

Example (4 Columns)

 

Actual July 2018

Actual August 2018

Actual September 2018

Forecast October 2018

 

I have prepared a visual based on these current column headers.

 

However next month when the report is run the column "Forecast October 2018" will become "Actual October 2018".

 

This may make the visual not work.

 

Is there a way to deal with changing column headers in source data? 

 

Thanks for you time.

 

J.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Data_Me_Up Frequent Visitor
Frequent Visitor

Re: Source Data Column Header Changes each month

Thank you for your time in replying to my query.

 

The method not exactly accommodated the query.

 

I was able to continue to search for an answer and found this solution http://power-bi-usergroup.blogspot.com/2015/12/dealing-with-tables-with-changing.html

 

This works for my purposes even when there is a change in column headers in the source data.

3 REPLIES 3
Super User
Super User

Re: Source Data Column Header Changes each month

The only suggestion i can make is to make use of the Transpose function in Power Query.  This would let you bring in the data each time, rotate the data such that your headings are now the first column.  I'd then add an index column in, move it to become the first column.  Rename each number in the Index to be your desired unchanging column names, then delete out the financial headings.

 

 

Transpose the table back and make sure to new headings are the column heading names.  From here, any import manipulation you were previously doing can be completed.

 

As long as your columns are always in the same order, this should work.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Super User
Super User

Re: Source Data Column Header Changes each month

Here is an example illustrating what i mean  (Data source is just a quick 'Enter Date' table).
You should be able to create a blank query and copy this code into the Advanced Editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYmcgdlGK1YlWcgWy3IDYHYg9wCKeQJYXEHsDsQ9YxBfI8gNifyAOUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Wrong Heading1" = _t, #"Wrong Heading2" = _t, #"Wrong Heading3" = _t, #"Wrong Heading4" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 0, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Set Index to Text" = Table.TransformColumnTypes(#"Reordered Columns",{{"Index", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Set Index to Text","3","Right Heading 4",Replacer.ReplaceText,{"Index"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","2","Right Heading 3",Replacer.ReplaceText,{"Index"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","1","Right Heading 2",Replacer.ReplaceText,{"Index"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","0","Right Heading 1",Replacer.ReplaceText,{"Index"}),
    #"Remove Bad Headings" = Table.RemoveColumns(#"Replaced Value3",{"Column1"}),
    #"Transposed Table Back" = Table.Transpose(#"Remove Bad Headings"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table Back", [PromoteAllScalars=true]),
    #"Set Field Types" = Table.TransformColumnTypes(#"Promoted Headers",{{"Right Heading 1", type text}, {"Right Heading 2", type text}, {"Right Heading 3", type text}, {"Right Heading 4", type text}})
in
    #"Set Field Types"

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Data_Me_Up Frequent Visitor
Frequent Visitor

Re: Source Data Column Header Changes each month

Thank you for your time in replying to my query.

 

The method not exactly accommodated the query.

 

I was able to continue to search for an answer and found this solution http://power-bi-usergroup.blogspot.com/2015/12/dealing-with-tables-with-changing.html

 

This works for my purposes even when there is a change in column headers in the source data.