Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Thanks in advance.
Solved! Go to 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |