Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Column Hierarchy Transformation

Hi all!    I hope you can guide me in the right direction.  I have a dataset that looks like this: I want to use this dataset in multiple graphs, and for that I want the source data to look...
  • audreygerred's avatar
    2 years ago

    Hello! I loaded your data into Power BI, then went to Power Query to transform: 

    The first step I did was to select the Kitchen and Service columns and unpivoted the columns which added Kitchen and Service to the rows and added a value column. 

    Next, I removed the value column (you are better off creating a total measure in Power BI) and moved the Attribute column to after business 

    Next, I selected all of the columns after Attribute and unpivoted columns

    And, finally, I removed the .1 in Attribute.1 column. 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIyBBIgDEEgljGECaLMlGJ1oOqMEApA2AiJBiuOjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Business = _t, Efficiency = _t, Economy = _t, Speed = _t, Kitchen = _t, Efficiency.1 = _t, Economy.1 = _t, Speed.1 = _t, Service = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Region", type text}, {"Business", type text}, {"Efficiency", Int64.Type}, {"Economy", Int64.Type}, {"Speed", Int64.Type}, {"Kitchen", Int64.Type}, {"Efficiency.1", Int64.Type}, {"Economy.1", Int64.Type}, {"Speed.1", Int64.Type}, {"Service", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Region", "Business", "Efficiency", "Economy", "Speed", "Efficiency.1", "Economy.1", "Speed.1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Region", "Business", "Attribute", "Efficiency", "Economy", "Speed", "Efficiency.1", "Economy.1", "Speed.1"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Region", "Business", "Attribute"}, "Attribute.1", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1",".1","",Replacer.ReplaceText,{"Attribute.1"})
    in
    #"Replaced Value"