Setting up a specific output based on List Data

[Gkr1981]

Hi All,

I have some list data, that I need to get exported into a specific format.

The list format contains multiple columns all of which are not populated.

Here is an example of the data

Machine	Month	Date	Shift	Crew	Activity 1	Activity 2		Location	Code	PROD 1	PROD 2
DL1	April	01/04/2020​	D	A	1​	5​	BCMP	ABG Pit	44505333	6436.571429​	0​
DL1	April	01/04/2020​	D	A		3​	CTON	ABG Pit	44505343​	0​	6000​

This is truncated by the way.

What I need to do is create another sheet which arranges this data in the following way

Activity

Machine

Measure

Date

D/N

Crew

Figure

However the data needs to look like this, so we can import it into a specific database table.

Activity	Machine	Measure	Date	D/N	Crew	Figure
44505333	DL1	Activity 1	1/04/2020	D	A	1
44505333	DL1	Activity 2	01/04/2020	D	A	5
44505333	DL1	PROD 1	01/04/2020	D	A	6436.57
44505343	DL1	Activity 2	01/04/2020	D	A	3
44505343	DL1	PROD 2	01/04/2020	D	A	6000

I have reduced the number of columns and just using Activity 1 / 2 (it goes up to 35 activity numbers) and there are 10 Prod numbers.

However, trying to do a Pivot Table doesn't bring it back in the format we require.

What is the best method to convert the data from the initial list format into the second style of list format?

Any assistance or guidance on what would be the best way forward to convert the data into the required format? Please note also there is a large number of pieces of equipment etc, so a table like this with predetermined fields would not be sustainable and new codes are changed daily.

Any assistance would be appreciated.

Cheers
Graeme

 

[sandy666]

if this is representative example try Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UOSC = Table.Unpivot(Source, {"Activity 1", "Activity 2", "PROD 1", "PROD 2"}, "Attribute", "Value"),
    TSC = Table.SelectColumns(UOSC,{"Code", "Machine", "Attribute", "Date", "Shift", "Crew", "Value"}),
    ParseDate = Table.TransformColumns(TSC,{{"Date", each Date.From(DateTimeZone.From(_)), type date}}),
    Type = Table.TransformColumnTypes(ParseDate,{{"Value", type number}}),
    Ren = Table.RenameColumns(Type,{{"Code", "Activity"}, {"Attribute", "Measure"}, {"Shift", "D/N"}, {"Value", "Figure"}}),
    Filter = Table.SelectRows(Ren, each ([Figure] <> 0))
in
    Filter