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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dehmos27
Frequent Visitor

How can I replace table column names before expanding data from multiple tables in a folder?

I am combining multiple tables in a folder. I am at the point where I can click Expand Data and expand all of the columsn. However, I need to rename some of the columns before expanding. Is there a way to rename column headers in the "table" values before expanding? As an example, I would want to rename "Name" to "Customer" so any tables with a column for "Name" expand into a single column, "Customer".

 

PBI Example.png

 

1 ACCEPTED SOLUTION

No problem, this should look like so then:

 

Table.TransformColumns(<PreviousStepName>, {{"Data", each Table.RenameColumns(_, Table.ToRows(Mappings), MissingField.Ignore)}})

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
ImkeF
Super User
Super User

Hi

just replace <PreviousStepName> by the name of your previous step:

 

Table.TransformColumns(<PreviousStepName>, {{"Data", each Table.RenameColumns(_,{{"Name", "Customer"}}, MissingField.Ignore)}})

MissingField.Ignore make sure that no error is thrown when the column "Name" doesn't exist in any of the tables. Also you can add more rename pair-lists if needed.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF ,

 

Wow. That is so very close (and really helpful). The only thing I need to do is change the {"Name","Customer"} block so the results are pulled from a separate table similar to a lookup.

 

In my example, I have a separate table called Mappings that is configured as follows:

 

Column          Corrected

Name             Customer

Location           Office

 

Is it possible to configure the code so that it checks the corresponding table for each column header and then renames if it finds a match?

 

Thank you again for your help.

No problem, this should look like so then:

 

Table.TransformColumns(<PreviousStepName>, {{"Data", each Table.RenameColumns(_, Table.ToRows(Mappings), MissingField.Ignore)}})

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

Can i follow up with this question? I have the samiliar issue. The original data comes with different 2nd column name "XXkisngihudg" but it is the same column saying "FileIDs". How to handle this issue? 

 

My data looks like this. PowerBi reads in using Row2 as default header. Can I use row 1 as default header, like "Column1", "Column2","Column3",.... This dynamic FileIDs gave me a headache to merge different data files. 

Screenshot (41).png

 

Thanks!

 

@ImkeF, all I can say is "Wow!" both to the speed of your response and to the solution. That worked like a charm. Thank you so very much. I was banging my head against a wall trying to figure that out.

RobbeVL
Impactful Individual
Impactful Individual

Hi there,

 

Could you make a copy of your code here?
Might be easier to have a look in.

 

Robbe

@RobbeVL,

 

Certainly. Here it is:

 

let
    Source = Folder.Files("C:\Users\rhetset\Desktop\PBI Samples"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from PBI Samples", each #"Transform File from PBI Samples"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from PBI Samples"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Table.ColumnNames([Transform File from PBI Samples])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Transform File from PBI Samples", "Data"}}),
    Custom1 = Table.TransformColumns(#"Renamed Columns",{{"Data",each Table.RenameColumns(_,{{"Name","Customer"}},MissingField.Ignore)}}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Table Name", "Customer", "Type", "Location", "Type1", "Item", "Site", "Product"}, {"Table Name", "Customer", "Type", "Location", "Type1", "Item", "Site", "Product"})
in
    #"Expanded Data"

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors