Row Header name change for visual

[NadeemAhamed]

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. 

[Alex87]

I cannot share the pbix. Simply copy paste the code provided in Power Query / new blank query, advanced editor and it will work. 

[Alex87 · Accepted solution]

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

[NadeemAhamed]

@Alex87 

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 

[fahadqadir3]

@NadeemAhamed 
test.pbix

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.

[NadeemAhamed]

@fahadqadir3 

I tried to replicate the same as you have done. but when iam trying to do "Sort by Column" its giving me the error. 

Could you help me out. 

[fahadqadir3]

@NadeemAhamed I add the index in table and hide it by using  white as text color. You can use @Alex87 method, its more efficient. Used my attached pbix in advance editor paste the M code.

https://1drv.ms/u/s!AjITNjmTPlzbhzDAlTKVCojvOlDy?e=NIfcPw

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.

[fahadqadir3]

@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.

[NadeemAhamed]

@fahadqadir3 

I have added the index column but its not working. 

kindly reveiew the pbix file https://toyotsu.box.com/s/hw1qe2swuanx7aaxo4m9lf5ddup3gx29

[Uzi2019]

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!

[NadeemAhamed]

@Uzi2019 

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

[Uzi2019]

Hi @NadeemAhamed 

I understood your problem.

you can try with this option under table visual

I hope it may resolved your issue!