Forum Discussion
Row Header name change for visual
Hi Everyone.
I have one table in that i have same row header name in multiple places such as Variance.
When we are bringing the data into PowerBI it is considering the same name row header in one name.
So we change the same row header name into unique such as variance1, variance2....
but we dont want to show the same row header name as variance1, variance2... in power bi
Need help from community, how to change the Row Header name as we can change the name for Column Header for Visual only.
This one was quite a challenge. Did it dynamically in Power Query : Pair the months and calculate variance for the second month in each pair. You do not need to rename your variances 1, 2 in the source table. Just keep it as in the initial raw data "Variance". Or even better, don't even include the row Variance. I added some tricky magic with empty space based on the number of the index to manage the naming to be always displayed "Variance" 😉 And the last Index column will help you to properly sort your table. Enjoy!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMjZRitWJVnJLTQJyTM3AnLDEoszEvORUoIiREVjEN7EIJA1R61hQhNCIpFbXyACquBLIszAHc7xKQbaYmGIoNjGCyueAFEN0OpamAznmBhiKDYFCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Report = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Report", Int64.Type}}), // Filter out the "Variance" rows #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Period] <> "Variance"), // Add Index column AddIndex = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type), // Pair the months and calculate variance for the second month in each pair AddPairIndex = Table.AddColumn(AddIndex, "PairIndex", each Number.IntegerDivide([Index], 2)), Grouped = Table.Group(AddPairIndex, {"PairIndex"}, {{"AllData", each _, type table [Period = text, Report = Int64.Type, Index = Int64.Type, PairIndex = Int64.Type]}}), CalculateVariance = Table.AddColumn(Grouped, "Variance", each let pair = [AllData], first = pair{0}?, second = pair{1}? in if second <> null then second[Report] - first[Report] else null), ExpandedVariance = Table.ExpandTableColumn(CalculateVariance, "AllData", {"Period", "Report", "Index"}), // Create a table for variance rows VarianceRows = Table.SelectRows(ExpandedVariance, each [Variance] <> null), VarianceTable = Table.AddColumn(VarianceRows, "MyPeriod", each Text.Repeat(" ", [Index]) & "Variance"), VarianceTableFinal = Table.SelectColumns(VarianceTable, {"MyPeriod", "Variance", "Index", "PairIndex"}), #"Renamed Columns" = Table.RenameColumns(VarianceTableFinal,{{"MyPeriod", "Period"}}), RenamedVarianceTable = Table.RenameColumns(#"Renamed Columns",{{"Variance", "Report"}}), #"Removed Duplicates" = Table.Distinct(RenamedVarianceTable, {"PairIndex"}), #"Added to Column" = Table.TransformColumns(#"Removed Duplicates", {{"PairIndex", each _ + 0.1, type number}}), // Combine the original and variance tables RemoveVarianceColumn = Table.RemoveColumns(ExpandedVariance, {"Variance"}), CombinedTables = Table.Combine({RemoveVarianceColumn, #"Added to Column"}), SortedCombined = Table.Sort(CombinedTables,{{"PairIndex", Order.Ascending}}), #"Changed Type1" = Table.TransformColumnTypes(SortedCombined,{{"PairIndex", type number}}), // Final sorting and renaming #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"PairIndex", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "PairIndex"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Report", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 0, 1, Int64.Type) in #"Added Index"If it answers your query, please mark my reply as the solution
11 Replies
This one was quite a challenge. Did it dynamically in Power Query : Pair the months and calculate variance for the second month in each pair. You do not need to rename your variances 1, 2 in the source table. Just keep it as in the initial raw data "Variance". Or even better, don't even include the row Variance. I added some tricky magic with empty space based on the number of the index to manage the naming to be always displayed "Variance" 😉 And the last Index column will help you to properly sort your table. Enjoy!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRMjZRitWJVnJLTQJyTM3AnLDEoszEvORUoIiREVjEN7EIJA1R61hQhNCIpFbXyACquBLIszAHc7xKQbaYmGIoNjGCyueAFEN0OpamAznmBhiKDYFCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Report = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type text}, {"Report", Int64.Type}}), // Filter out the "Variance" rows #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Period] <> "Variance"), // Add Index column AddIndex = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type), // Pair the months and calculate variance for the second month in each pair AddPairIndex = Table.AddColumn(AddIndex, "PairIndex", each Number.IntegerDivide([Index], 2)), Grouped = Table.Group(AddPairIndex, {"PairIndex"}, {{"AllData", each _, type table [Period = text, Report = Int64.Type, Index = Int64.Type, PairIndex = Int64.Type]}}), CalculateVariance = Table.AddColumn(Grouped, "Variance", each let pair = [AllData], first = pair{0}?, second = pair{1}? in if second <> null then second[Report] - first[Report] else null), ExpandedVariance = Table.ExpandTableColumn(CalculateVariance, "AllData", {"Period", "Report", "Index"}), // Create a table for variance rows VarianceRows = Table.SelectRows(ExpandedVariance, each [Variance] <> null), VarianceTable = Table.AddColumn(VarianceRows, "MyPeriod", each Text.Repeat(" ", [Index]) & "Variance"), VarianceTableFinal = Table.SelectColumns(VarianceTable, {"MyPeriod", "Variance", "Index", "PairIndex"}), #"Renamed Columns" = Table.RenameColumns(VarianceTableFinal,{{"MyPeriod", "Period"}}), RenamedVarianceTable = Table.RenameColumns(#"Renamed Columns",{{"Variance", "Report"}}), #"Removed Duplicates" = Table.Distinct(RenamedVarianceTable, {"PairIndex"}), #"Added to Column" = Table.TransformColumns(#"Removed Duplicates", {{"PairIndex", each _ + 0.1, type number}}), // Combine the original and variance tables RemoveVarianceColumn = Table.RemoveColumns(ExpandedVariance, {"Variance"}), CombinedTables = Table.Combine({RemoveVarianceColumn, #"Added to Column"}), SortedCombined = Table.Sort(CombinedTables,{{"PairIndex", Order.Ascending}}), #"Changed Type1" = Table.TransformColumnTypes(SortedCombined,{{"PairIndex", type number}}), // Final sorting and renaming #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"PairIndex", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "PairIndex"}), #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns",{{"Report", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 0, 1, Int64.Type) in #"Added Index"If it answers your query, please mark my reply as the solution
Sorry to saying this,
Iam not able to follow the above steps. could you able to share the Pbix file that will be very helpfull
Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.
Hi NadeemAhamed
your default name ( changed name) will be display on all visual. if you dont want to showcase the default name like variance1 so need to change for each and every visual.
like below
double click on name and change the name
or click on arrow and rename the field name
you can add description like variance 1 varaince 2 by keeping the Report name of the column.
once you hover on it you get to know Variance 1 of report name.
I hope i answered your question!
Thank you for your valuable time to read and reply my post.
Sorry i think your talking about how to change the column header name.
but my query is to change the Row header.
I want to show the Variance of every 2 month in the table as it is in excel sheet.
We have to show our table report same as excel sheet.
I have attached the Pbix and Excel file for your reference.
Pbix file: https://toyotsu.box.com/s/hw1qe2swuanx7aaxo4m9lf5ddup3gx29
Excel file: https://toyotsu.box.com/s/3o3bskd5ynzqqy9t9a6oofoo0d5nhwpm
Hi NadeemAhamed
I understood your problem.
you can try with this option under table visual
I hope it may resolved your issue!
NadeemAhamed You can add Index Column in table to get the desired results. Review the attached screenshot:
Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.I have added the index column but its not working.
kindly reveiew the pbix file https://toyotsu.box.com/s/hw1qe2swuanx7aaxo4m9lf5ddup3gx29
I cannot share the pbix. Simply copy paste the code provided in Power Query / new blank query, advanced editor and it will work.
