Transform Data

Hi PBIuser_23 ,

Please see if this meet your needs:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZJNCoNADIWvUmbtQhPxBELPYGWWLmbjiKWFub3UthDjGzOLQB4P8uVvHN0jztOtcZXrwzs8Q5x3cV/ja9mzWkUSOfKP4atrAqmqzS//BwO/Fj6ZBAZ9JVGRgF8yA11uSZJkz3prX98ifLIB9Iy2gu9iEbiAoP2jtggt2EoSmoDOzED40t3pN2xi5g4ZAoOKJZe3CPiXcAVrRkzgE0FXKNfebw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Zone = _t, #"Division " = _t, #"Group " = _t, #"Product Criteria 1" = _t, #"Product criteria 2" = _t, #"Product criteria 3" = _t, #"Product Name 1" = _t, #"Product Criteria 1.1" = _t, #"Product criteria 2.1" = _t, #"Product criteria 3.1" = _t, #"Product Name 2" = _t, #"Product Criteria 1.2" = _t, #"Product criteria 2.2" = _t, #"Product criteria 3.2" = _t, #"Product Name 3" = _t, #"Product Criteria 1.3" = _t, #"Product criteria 2.3" = _t, #"Product criteria 3.3" = _t, #"Product Name 4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Zone", type text}, {"Division ", type text}, {"Group ", type text}, {"Product Criteria 1", type text}, {"Product criteria 2", type text}, {"Product criteria 3", type text}, {"Product Name 1", Int64.Type}, {"Product Criteria 1.1", type text}, {"Product criteria 2.1", type text}, {"Product criteria 3.1", type text}, {"Product Name 2", Int64.Type}, {"Product Criteria 1.2", type text}, {"Product criteria 2.2", type text}, {"Product criteria 3.2", type text}, {"Product Name 3", Int64.Type}, {"Product Criteria 1.3", type text}, {"Product criteria 2.3", type text}, {"Product criteria 3.3", type text}, {"Product Name 4", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Division ", "Zone", "Group "}, "Attribute", "Value"),
#"Added Custom1" = Table.AddColumn(#"Unpivoted Other Columns", "AttributeProper", each Text.Proper([Attribute])),
#"Added Custom" = Table.AddColumn(#"Added Custom1", "Custom", each if Text.From([Value])="0" then [Value] else [AttributeProper]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[AttributeProper]), "AttributeProper", "Custom"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", List.Select(Table.ColumnNames(#"Pivoted Column"),each Text.Contains(_,"Product Criteria")= false), "Attribute", "Product Criteria"),
#"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", List.Select(Table.ColumnNames(#"Unpivoted Columns"),each Text.Contains(_,"Product Name")= false), "Attribute.1", "Product Name"),
#"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Columns1",{"Attribute", "Attribute.1"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Product Criteria] <> "0") and ([Product Name] <> 0))
in
#"Filtered Rows"

A sample PBIX. for your reference is attached.

If I have not understood your needs correctly, please do not hesitate to inform me.

Hope it helps,

Community Support Team _ Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.