Finding the name of a column in a Power Query

mitchandsuzy
Posts: 20
Joined: Tue Nov 17, 2015 4:00 pm

Finding the name of a column in a Power Query

Postby mitchandsuzy » Fri Oct 28, 2016 4:55 pm

Good Morning or Afternoon,

Thank you for taking the time to read this!! I have searched everywhere I know and cannot find an answer.

After attending one of Matt and Iman's Power BI Meet-ups, Matt showed us how to combine several files in a directory to create a single query (data-set)

This is great except that I wanted to keep the name of the file in the data-set table just in case I wanted to trace back where the data came from.

My problem is if the 1st record is not the same one every time (PromoteHeaders) I cannot rename the Column to "Source of Data" because I don't know what column1's name will be. How can I find out the name of a column(field) in power query???

I have asked google in every way I can think of to discover if a column has properties that I can get access to programatically but either my imagination is not good enough to come up with the correct question or maybe it is not possible (???? as unlikely as I think).

If anyone has an idea please let me know.

Thanks again
Suzanne

MattAllington
Posts: 929
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Finding the name of a column in a Power Query

Postby MattAllington » Fri Oct 28, 2016 5:30 pm

Hi Suzy. I'm sure it is possible, but I am not totally clear of the issue. Can you describe the steps you are completing. Are you loading csv of Excel?
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

mitchandsuzy
Posts: 20
Joined: Tue Nov 17, 2015 4:00 pm

Re: Finding the name of a column in a Power Query

Postby mitchandsuzy » Sat Oct 29, 2016 3:21 pm

Thanks Matt,

I am combining several xlsx files. I keep the filename in the first column to make it easier to id where the data came from.

Here is the code I used

Code: Select all

let
    SolutionPath = fnGetParameter("File Path"),
    Source = Folder.Files(SolutionPath),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Name", Text.Lower}}),
    #"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each Text.EndsWith([Name], "1060.xlsx")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Data", "Item", "Kind"}, {"Custom.Name", "Data", "Item", "Kind"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each [Kind] = "Sheet"),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Name", "Custom.Name", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data"),
    #"Removed Columns3" = Table.RemoveColumns(#"Promoted Headers",{"Sheet1", "Column21"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns3",{{"2013.14 - zmrs1060.xlsx", type text}, }),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([Project Definition] <> "Project Definition")),
    #"Trimmed Text" = Table.TransformColumns(#"Filtered Rows2",{{"Project Definition Text", Text.Trim}}),
    #"Uppercased Text" = Table.TransformColumns(#"Trimmed Text",{{"Project Definition Text", Text.Upper}}),
    #"Renamed Columns" = Table.RenameColumns(#"Uppercased Text",{{"2013.14 - zmrs1060.xlsx", "Source File"}})
in
    #"Renamed Columns"


"2013.14 - zmrs1060.xlsx" in this instance is the filename in the 1st row and that becomes the header. I have always avoided hard coding things like this but I have not been able to find a way to dynamically get the name of the first column so I can change "whatever it is" to "Source File".

Thanks,
Suzy

MattAllington
Posts: 929
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Finding the name of a column in a Power Query

Postby MattAllington » Sun Oct 30, 2016 4:44 am

Thanks Suzy

I can now reproduce the issue. There may be a more funky/technical solution available, however it occurs to me there is a simple manual solution. Instead of promoting the first row as headers, why not just manually rename the headers and delete the first row?

Next time you are at our PUG, please come and say hi so I can put a face to your mitchandsuzy "name" :-)

Matt
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

mitchandsuzy
Posts: 20
Joined: Tue Nov 17, 2015 4:00 pm

Re: Finding the name of a column in a Power Query

Postby mitchandsuzy » Mon Oct 31, 2016 12:17 pm

Thanks Matt,

I hadn't thought of that! I get so involved with finding a solution sometimes.

I would prefer to have dynamic column names so if you come across a method of finding a column name that would be great.

If I trip over it I will let you know as well.

Suzy

hohlick
Posts: 11
Joined: Wed Oct 26, 2016 1:22 am
Location: St.Petersburg, Russia
Contact:

Re: Finding the name of a column in a Power Query

Postby hohlick » Wed Nov 02, 2016 6:38 pm

mitchandsuzy wrote:I would prefer to have dynamic column names so if you come across a method of finding a column name that would be great.


Hi Suzy.
Did you meant these rows?

Code: Select all

 #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns3",{{"2013.14 - zmrs1060.xlsx", type text}, }),
 ...
 #"Renamed Columns" = Table.RenameColumns(#"Uppercased Text",{{"2013.14 - zmrs1060.xlsx", "Source File"}})

then you can use Table.ColumnNames function. It returns names of columns in a table as a list of strings, and you can use {0} reference to address first column (and I recommend to rename it just after you promote headers):

Code: Select all

 #"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{Table.ColumnNames(#"Removed Columns3"){0}, "Source File"}})

But Matt's solution (rename columns without headers promotion) is really better
Regards,
Maxim Zelensky

MattAllington
Posts: 929
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Finding the name of a column in a Power Query

Postby MattAllington » Thu Nov 03, 2016 4:41 am

Thanks for sharing Maxim. That was exactly he funky/technical solution I assumed existed. I have now added this to my growing list of PQL code snippets
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

mitchandsuzy
Posts: 20
Joined: Tue Nov 17, 2015 4:00 pm

Finding the name of a column in a Power Query

Postby mitchandsuzy » Thu Nov 03, 2016 11:58 am

Thanks so much Maxim!!!! :D

I was trying to use Table.ColumnNames but could not figure out how to do it properly, I wish I had kept what I was doing so I could see what I did wrong - it just kept telling me I needed a list or something similar...

I give you the "Hero of the Day Award" you will save me a lot of time in future.

Thanks so very much!!!!

Suzy

hohlick
Posts: 11
Joined: Wed Oct 26, 2016 1:22 am
Location: St.Petersburg, Russia
Contact:

Re: Finding the name of a column in a Power Query

Postby hohlick » Fri Nov 04, 2016 1:29 am

You are welcome :)
Regards,
Maxim Zelensky

PhilC
Posts: 145
Joined: Tue Sep 09, 2014 8:13 am

Re: Finding the name of a column in a Power Query

Postby PhilC » Tue Jul 11, 2017 7:59 am

Just wanted to say this solution keeps on giving, thanks very much.

Cheers
Phil


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 4 guests

cron