cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
New Member

Re: Create New Column using the text in a Column Header in Power Query

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
Highlighted
Super User III
Super User III

Re: Create New Column using the text in a Column Header in Power Query

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





Highlighted
New Member

Re: Create New Column using the text in a Column Header in Power Query

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

Highlighted
New Member

Re: Create New Column using the text in a Column Header in Power Query

I would've provided the M Code but it's on a restricted computer. (Sorry All)

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors