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
ghorta
New Member

Visuals not showing after changing the source excel file column header

Hello,

 

I'm trying to create a PowerBI report that is able to cope with column changes on the Excel source file. For that, I have already created a variable for the column name list to reference inside the TransformColumnTypes() function in order to avoid using the column name.

 

"menu_Table = Source{[Item="menu",Kind="Table"]}[Data],
columnList = Table.ColumnNames(menu_Table),
#"Changed Type" = Table.TransformColumnTypes(menu_Table, {
{columnList {0}, Int64.Type},
{columnList {1}, type text},
{columnList {2}, type number}
})" 
 
 The code above has solved the issue "the column of the table wasn't found". However, now the dashboard that is making use of the column header that was changed is causing the problem below. Can anyone help me with this issue?
 
 

Anotação 2020-08-17 162610.png

 
 
 
 
 
 
 
 

Thanks in advance.

1 ACCEPTED SOLUTION

Hi @ghorta ,

 

Looking at your code I believe tha the best option is not to promote the headers but to remove the first 4 rows (sop innclude the header) and then make a rename of the columns

 

Check code below:

 

let
Source = Excel.Workbook(File.Contents("Source.xlsx"), null, true),
#"Excel_Sheet" = Source{[Item="Excel_Sheet",Kind="Sheet"]}[Data],
#"Removed Top Rows1" = Table.Skip(#"Excel_Sheet",4),
columnList = Table.ColumnNames(#"Removed Top Rows1"),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Top Rows1", {
{columnList {0}, Int64.Type}, //Column1
{columnList {1}, type text}, //Column2
{columnList {2}, type text} //Column3
}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1", "NAME1"}, {"Column2", "NAME2"}, {"Column3", "NAME3"}})
in
#"Renamed Columns"


 

This should give you expected result, further more having this made like previously you don't even need to have the changed type by table transformantion but can be done directly so the code could be one of the two in the bottom:

 

Renaming is the last step:

let
ource = Excel.Workbook(File.Contents("Source.xlsx"), null, true),
#"Excel_Sheet" = Source{[Item="Excel_Sheet",Kind="Sheet"]}[Data],
#"Removed Top Rows1" = Table.Skip(#"Excel_Sheet",4),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows1",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "NAME1"}, {"Column2", "NAME2"}, {"Column3", "NAME3"}})
in
#"Renamed Columns"

 

Renaming before formatting, I would do this because then the naming would be correct troughout the rest of the changes easier to follow up on the next steps 

let
ource = Excel.Workbook(File.Contents("Source.xlsx"), null, true),
#"Excel_Sheet" = Source{[Item="Excel_Sheet",Kind="Sheet"]}[Data],
#"Removed Top Rows1" = Table.Skip(#"Excel_Sheet",4),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows1",{{"Column1", "NAME1"}, {"Column2", "NAME2"}, {"Column3", "NAME3"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"NAME1", Int64.Type}, {"NAME2", type text}, {"NAME3", type text}})
in
    #"Changed Type"

 

Hope you don't take this in the wrong way but sometimes is better to simplify and on this case the best option is not to promote the headers but remove them from the data and rename columns.


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



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

Hi @ghorta ,

 

On the query editor when you finish the treatment the name of the columns you have is based on the new excel names or are they always the same.

 

Explaining myself better what's is the scenario on the query editor:

 

1. Excel files changes column names to ColumnA, Column B and your query assumes the ColumnA, ColumnB

2. Excel files changes column names to ColumnA, Column B and your query renames the columns to a preset value like ColumnStandardA, ColumnStandardB

 

If you are dinamicallly changing the name of your columns on the query editor and the final result is the same table but with different headers when you get back to your visualization the link between the visualizaiton and your table get lost because PBI does not recognize the change of names in the columns.

 

I think that altough you have a change of name in the excel you need to add a rename step on your query so that the final column names are always the same so you will not loose you visualizaitons, measures and so on.


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



Hello @MFelix 

 

thanks for your help.

 

My query is fetching the name of the columns without calling the actual name of the column, otherwise it would return an error saying that the column wasn't found because the column name had changed. So, instead of using

 

" //#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", Int64.Type}, {"Column2", type text},..."

 

I'm avoiding using the actual column names on the query by using the following example:

 

"let
Source = Excel.Workbook(File.Contents("Source.xlsx"), null, true),
#"Excel_Sheet" = Source{[Item="Excel_Sheet",Kind="Sheet"]}[Data],
#"Removed Top Rows1" = Table.Skip(#"Excel_Sheet",3),
#"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
columnList = Table.ColumnNames(#"Promoted Headers1"),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1", {
{columnList {0}, Int64.Type}, //Column1
{columnList {1}, type text}, //Column2
{columnList {2}, type text} //Column3
})
in
#"Changed Type2"

"

 

And this works well as even if I change the column name on excel source file, it works. The problem is on the visuals that is calling the column name that has changed as it does not update for the new name.

 

Can you please explain how can I follow your suggestion and add a "rename step on your query so that the final column names are always the same so you will not loose you visualizaitons..."?

 

Your help is much appreciated! Thanks a lot 🙂

 

 

Hi @ghorta ,

 

Looking at your code I believe tha the best option is not to promote the headers but to remove the first 4 rows (sop innclude the header) and then make a rename of the columns

 

Check code below:

 

let
Source = Excel.Workbook(File.Contents("Source.xlsx"), null, true),
#"Excel_Sheet" = Source{[Item="Excel_Sheet",Kind="Sheet"]}[Data],
#"Removed Top Rows1" = Table.Skip(#"Excel_Sheet",4),
columnList = Table.ColumnNames(#"Removed Top Rows1"),
#"Changed Type2" = Table.TransformColumnTypes(#"Removed Top Rows1", {
{columnList {0}, Int64.Type}, //Column1
{columnList {1}, type text}, //Column2
{columnList {2}, type text} //Column3
}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1", "NAME1"}, {"Column2", "NAME2"}, {"Column3", "NAME3"}})
in
#"Renamed Columns"


 

This should give you expected result, further more having this made like previously you don't even need to have the changed type by table transformantion but can be done directly so the code could be one of the two in the bottom:

 

Renaming is the last step:

let
ource = Excel.Workbook(File.Contents("Source.xlsx"), null, true),
#"Excel_Sheet" = Source{[Item="Excel_Sheet",Kind="Sheet"]}[Data],
#"Removed Top Rows1" = Table.Skip(#"Excel_Sheet",4),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows1",{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "NAME1"}, {"Column2", "NAME2"}, {"Column3", "NAME3"}})
in
#"Renamed Columns"

 

Renaming before formatting, I would do this because then the naming would be correct troughout the rest of the changes easier to follow up on the next steps 

let
ource = Excel.Workbook(File.Contents("Source.xlsx"), null, true),
#"Excel_Sheet" = Source{[Item="Excel_Sheet",Kind="Sheet"]}[Data],
#"Removed Top Rows1" = Table.Skip(#"Excel_Sheet",4),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows1",{{"Column1", "NAME1"}, {"Column2", "NAME2"}, {"Column3", "NAME3"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"NAME1", Int64.Type}, {"NAME2", type text}, {"NAME3", type text}})
in
    #"Changed Type"

 

Hope you don't take this in the wrong way but sometimes is better to simplify and on this case the best option is not to promote the headers but remove them from the data and rename columns.


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



Hello @MFelix,

 

Thanks a lot for your help on this!

 

Of course I accept your suggestions! I can see the improvement using your approach!

 

I have tested it and I can see that the visuals continue to work after I updated the excel columns from the source file.

 

Once again, thanks a lot.

 

Best regards, 

@ghorta ,

 

Glad I could help, I had this issue many times on previous files and in the end the answer was rigth in front of me just by not overthinking things so it's a common path we all take.

 

👍👍👍👍👍


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



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.