Transform Data

[PBIuser_23]

Hi Community

I need to create a Fact table based on lengthy columnar data. Need to reduce the repeating columns and summarize based on a condition.

Input Data

My input data looks like the image "Input data".  This is a dynamic data set comprising for 3 product criteria per product.  Need to transform the data as follows

step 1: if there is a "Y" in the product criteria column, then it needs to be replaced with the header name of product criteria fields

step 2: The non zero values in the product name needs to be replaced with the header name (which is the product name)

step 3: The non zero records needs to summarised by product criteria and product name. Illustrative output is provided in the image above.

Thanks for your assistance.

PS: Newbie to the community.  Could not find an option to attach the excel containing the dummy data to this post.

Spoiler

[v-xiaoyan-msft · Accepted solution]

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.

[PBIuser_23]

Dummy Data for the above topic