Forum Discussion
M Language - Transform value when it contains a particular character
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
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
5 Replies
- ImkeF
Community Champion
Just to make sure my understanding is correct: You want to rename all columns except the "Employee#"-Column?- ImkeFHave 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.