Change Column Headers to references in lookup table

[EpicTriffid]

Hi all,

So I have a set of data laid out like below:

Item	2016/17	2017/18	2018/19	2019/20	2020/21
A	1	2	4	1	6
B	4	5	6	4	7
C	7	3	8	5	3

I just ended up finding out that the five years of data listed above will change every year, shifting forward by one. So the 2016/17 column will be dropped and the 2021/22 column will be added in the excel spreadsheet. Bit annoying as any later step in my query that references those headers will break. 

So I am thinking that when the data gets updated in the spreadsheet I can put it in under the headers of Year 1, Year 2, Year 3 and so on, then I can have a lookup table that lists the actual years in it, like:

2017/18

2018/19

2019/20

2020/21

2021/22

Then I will be able to add a step in shortly after the "Source" step that converts the headers to each row in my lookup table, so "Year 1" will change to the first row of my lookup table ("2017/18"), "Year 2" will change to the second row ("2018/19") and so on.

However, I have absolutely no idea how to do that. Can I get M code to do that?

[BA_Pete]

Hi @EpicTriffid ,

This is an XY Problem.

You don't need to do what you think you do with the column names.

You should select your [Item] column then go to the transform tab > Unpivot Columns (dropdown) > Unpivot Other Columns.

This will put your data into the following structure:

This means you only ever have to reference the generic [Attribute] and [Value] column names and is therefore dynamic for changing years. You can change these names by editing these parts of the code generated by the unpivot step:

It also structures your data in the correct/most efficient/best practice structure for reporting.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMgNoGyzZRidaKVnKAipmARCNscLOMMZukoGQOxBVSFsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"2016/17" = _t, #"2017/18" = _t, #"2018/19" = _t, #"2019/20" = _t, #"2020/21" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Pete

[Vijay_A_Verma · Accepted solution]

Actually, you need not make a reference table. Just demote your headers, Transpose, look if your header contains "/", change it to Year1....5 and then Transpose and promote your headers again.

This way it will be completely dynamic and having no dependency on another table maintenance.

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYiMgNoGyzZRidaKVnKAipmARCNscLOMMZukoGQOxBVSFsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, #"2016/17" = _t, #"2017/18" = _t, #"2018/19" = _t, #"2019/20" = _t, #"2020/21" = _t]),
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ColumnNames", each if Text.Contains([Column1],"/") then "Year"&Text.From([Index]) else [Column1]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Column1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ColumnNames", "Column2", "Column3", "Column4"}),
    #"Transposed Table1" = Table.Transpose(#"Reordered Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item", type text}, {"Year1", Int64.Type}, {"Year2", Int64.Type}, {"Year3", Int64.Type}, {"Year4", Int64.Type}, {"Year5", Int64.Type}})
in
    #"Changed Type1"

[jennratten]

Hello - I recommend saving your column names in columns instead of rows.  It makes it a little more straight forward to convert it to a list of headers.  You can use Table.RenameColumns and then provide a list of the old columns and the new columns.  This is how you can do it.

New Column Headers in Columns:

Sample Data with Columns to be Renamed:

Sample Data with the Columns Renamed:

Script (copy this and paste it into a blank query):

let
    // Headers in Rows
    HeadersInRows = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ3MlLSUfJLLXfOzynNzTNE5qDIGCvF6oC1GOkbGSNLOCJznJA5zkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Column1 = _t, Column2 = _t, Column3 = _t]),

    // Headers in Columns
    HeadersInColumns = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstd87PKc3NM1TSQXAclWJ1kOSMkOWcUOWMIXLJYI6zUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2021/22" = _t, #"2022/23" = _t]),

    // Data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkpNUdJRMgTixIKCnFSlWJ1opaSc0lSggBEQF6QmFoHF0otSU/OAAsZAnJSYB4RKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Color = _t, Number = _t, Fruit = _t]),

    // RenameColumns
    RenameColumns = Table.RenameColumns ( Source, 
        List.Zip (
            {
                Table.ColumnNames ( Source ), // old header names
                HeadersInColumns[#"2022/23"]  // new header names
            }
        )
    )
in
    RenameColumns