Create new table with the help of existing one

[satyanarayan]

Hi

My requirment is as per follwing. I have a table daily Production, so i need to find stopage reason in row table as per given example.

Date	Production Line	Production Qty	Total Machine Time	Unit	Stopage time 1	Unit	Reason For Stopage 1	Stopage time 2	Unit	Reason For Stopage 2
01-Jan-20	10001 cream	1400	200	MIN	50	MIN	Electrical Problem	20	Min	Machanical Problem

 I Want as per follwing new table

Date	Production Line	Production Qty	Total Machine Time	Unit	Stopage time 1	Unit	Reason For Stopage 1
01-Jan-20	10001 cream	1400	200	MIN	50	MIN	Electrical Problem
01-Jan-20	10001 cream	1400	200	MIN	20	Min	Machanical Problem

Thank in advance

[Anonymous]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zUyUNJRMjQwMDBUSC5KTcwF8UwMQIJGYNLX0w9ImiKYrjmpySVFmcmJOQoBRflJOam5YLUg+cw8EJmYnJGYhyIfGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Production Line" = _t, #"Production Qty" = _t, #"Total Machine Time" = _t, Unit = _t, #"Stopage time 1" = _t, Unit.1 = _t, #"Reason For Stopage 1" = _t, #"Stopage time 2" = _t, Unit.2 = _t, #"Reason For Stopage 2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Production Line", type text}, {"Production Qty", Int64.Type}, {"Total Machine Time", Int64.Type}, {"Unit", type text}, {"Stopage time 1", Int64.Type}, {"Unit.1", type text}, {"Reason For Stopage 1", type text}, {"Stopage time 2", Int64.Type}, {"Unit.2", type text}, {"Reason For Stopage 2", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Production Line", "Production Qty", "Total Machine Time", "Unit", "Stopage time 1", "Unit.1", "Stopage time 2", "Unit.2"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Date", "Production Line", "Production Qty", "Total Machine Time", "Unit", "Unit.1", "Unit.2", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns1", "Should Keep", each (Text.Contains([Attribute], "1") and Text.Contains([Attribute.1], "1")) or (Text.Contains([Attribute], "2") and Text.Contains([Attribute.1], "2"))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Should Keep] = true)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Reason For Stoppage"}, {"Value.1", "Stoppage Time"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute", "Attribute.1", "Should Keep", "Unit.1", "Unit.2"})
in
    #"Removed Columns"

Just paste into Power Query and see step by step how it works. Then adjust to your requirements.

Best

D

[satyanarayan]

code paste in query it work. but when add more line item in table1 result of table 2 is not changed. it is alway stastic. 

[Anonymous · Accepted solution]

Use the UNPIVOT function in Power Query.

Best
D

[satyanarayan]

Can you explain how to use this fuction on this table.

[venal]

As suggested, i tried the same. But it is not working.

I am also having the same issue.