How to calculate if the data is in the multiple columns

[york]

Hi guys:

       Like below table, operator will inspect the product every day, and they will record the defects they found in column Defect 1~4

Inspection Date	Defect 1	Defect 2	Defect 3	Defect 4
2023/1/1	Box Broken	Soldering Fail	Low Battery	Product Scratch
2023/1/2	Low battery	Product Scratch
2023/1/3	Product broken	Low battery
2023/1/4	Cable Disconnected

     Then I want to create a chart that shows the data, such as how many types of defects are found, how many times each defect is found, and the trend by date of each defect type, could you help check how to meak it?

[v-stephen-msft · Accepted solution]

Hi @york ,

@jennratten 's solution is done in Power Query and the key is to unpivot columns.

Click "Transform data" to go to Power Query Editor.

In Power Query, select the below four columns at the same time and click "Unpivot Columns".

And then the four columns are converted to a Attribute column and a Value column.

Then click "Close&Apply" to go back to Power BI Desktop, create the below matrix.

Best Regards,

Stephen Tao

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

[jennratten]

Hello - I recommend unpivoting the columns like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY31DdU0lFyyq9QcCrKz07NA3KC83NSUosy89IV3BIzc4ACPvnlCk6JJSWpRZVAXkBRfkppcolCcHJRYklyhlKsDtwoI6jiJJyKdZQUwBhJkzGSsiSYGxRQjcHQZAIUcE5MyklVcMksTs7Py0tNLklNgasEq44FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Inspection Date" = _t, #"Defect 1" = _t, #"Defect 2" = _t, #"Defect 3" = _t, #"Defect 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Inspection Date", type date}, {"Defect 1", type text}, {"Defect 2", type text}, {"Defect 3", type text}, {"Defect 4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Inspection Date"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " "))
in
    #"Filtered Rows"

You can then add the data to a visual:

[york]

Hi Jenn:

  Thank you very much for your reply, I'm new in Power BI, so I want to know where to input the code that you provided in the Power BI Desktop?

   And I have a table not only contain the columns of the Inspection date, defects, but also the products name, product SKU, inspection result... And I hope the chart can be created in the report view, and the defects may be can be shown in the chart by date/product type/product name...