Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Really sorry, I re-formatted my initial message because I was using Chrome and they whole line breaks and indentations got erased...
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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thanks ImkeF.
It works.
But I used the unpivot-way.
You are right. It is much better.
Jason.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |