Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tini-Bee
New Member

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.FillDown_ColumnHeader.png

1 ACCEPTED 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. 

View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.