Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |