Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi 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)
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |