Forum Discussion
Create New Column using the text in a Column Header in Power Query
I have a Column Header [Tiny_Bee] in Power Query. There are no values in this column. I'd like to use the column header string to create a Custom Column called [Business Unit] with values based on the Column Header string, [Tiny_Bee]. see image
Future transformations can/ may have different Headers hence the reason I want to capture the string in the Column Header rather than just create a Custom Column and filling "Tiny_Bee"
Your assistance is appreciated.
Hi Miguel, The problem with the solution is the list of Table headers; they could be over 10000. Based on current files the column/table header I'm referring to is the 1st one. I'll try your solution and possibly modify it to select the first column.
Update - Thanks to your advice I had a good session understanding the syntax of DAX Queries (M Code) and found an easier solution; I simply went back to the Navigation step, instead of promoting header, created a Custom Column to filter the Table column for particular text, filtered off that text then filled down 1st Table Column and renamed it.
3 Replies
HI Tini-Bee ,
This suggestion may need adaptation basically depending on the header number of your data you can use the Table.ColumNames to get a list of your table headers and then filter out the column you need and use the value on a new column.
Check an example of code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRSq6qUIrVATOrksuUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Tiny Bee" = _t, #"Big Bee" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Tiny Bee", type text}, {"Big Bee", type text}}), Custom1 = Table.FromList(List.FirstN(Table.ColumnNames(#"Changed Type"),1), Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {#"Custom1"}), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Expanded Custom1" = Table.ExpandTableColumn(#"Expanded Custom", "Custom", {"Column1"}, {"Custom.Column1"}) in #"Expanded Custom1"Again depending on the location of specific column you are refering this need adaptations.
Hi Miguel, The problem with the solution is the list of Table headers; they could be over 10000. Based on current files the column/table header I'm referring to is the 1st one. I'll try your solution and possibly modify it to select the first column.
Update - Thanks to your advice I had a good session understanding the syntax of DAX Queries (M Code) and found an easier solution; I simply went back to the Navigation step, instead of promoting header, created a Custom Column to filter the Table column for particular text, filtered off that text then filled down 1st Table Column and renamed it.
I would've provided the M Code but it's on a restricted computer. (Sorry All)
