Source Data Column Header Changes each month

[Data_Me_Up]

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.

[Anonymous]

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.

[Data_Me_Up · Accepted solution]

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.

[Anonymous]

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"