M Language - Transform value when it contains a particular character

[Anonymous]

Guys,

Need help with the following:

I have the following table in PowerQuery (sorry...but I don't know how to attach pictures when writing a new post):

Column 1     Column 2       Column 3         Column 4

Employee#   2018/001       2018/002         2018/003

1234567           $5000              $8000           $10,000

2345677           $8000              $9000           $14,000

What I want to do is automatically rename the column header based on the value of the first row.

Example: Column 2 would be renamed "Month 1" based on the value "2018/001".

The reason I want to do this is because at each refresh, the number of column might not be the same. You could have 5 or 10 months, and some months in between might even be missing. So I want a dynamic re-naming of the column so that I can later use a DAX relationship to link the money column to the master data.

Any ideas ?

Thanks,

Jason

[ImkeF]

Just to make sure my understanding is correct: You want to rename all columns except the "Employee#"-Column?

[ImkeF]

Have you considered unpivoting the months-columns? This is usually a better practice for DAX. Column names are identifieres and if you cannot specify them in beforehand, you would have problems to refer to them in DAX.

[Anonymous]

Really sorry, I re-formatted my initial message because I was using Chrome and they whole line breaks and indentations got erased...

[ImkeF · Accepted solution]

Please try this code (although I still would recommend the unpivot-way):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1VVtJRMjIwtNA3MDBEMI0QTGOlWJ1oJUMjYxNTM3OgsIqpgYEBiLaA0oYGOiAWSBVEkTmyrCVMlQlEVSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    FirstRow = #"Changed Type"{0},
    RecordToList = Record.FieldValues(FirstRow),
    SkipFirstItem = List.Skip(RecordToList,1),
    CreateNewColNames = List.Transform(SkipFirstItem, each "Column" & Text.Split(_, "/"){1}),
    CombineWithFirstCol = {List.First( RecordToList)} & CreateNewColNames,
    Rename = Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), CombineWithFirstCol})),
    SkipFirstRow = Table.Skip(Rename,1)
in
    SkipFirstRow

[Anonymous]

Thanks ImkeF.

It works.

But I used the unpivot-way.

You are right. It is much better.

Jason.