Pivot repeating columns names in PQ

[hansolu]

Hi

i do have a log file from one equipment. Short description. Many stations, each station has a number, Temperatur, Time,..
The log file is creating one row for the whole line.
At the beginning of the row is see the time go in and the time go out of the line, the flightbar number (=batch) and then each station used with the parameter (temperature, time,..) for each station.

The problem now is I want to analyse the data and get the data in a better way to do so. This means I need a table with the basic data for each Flightbar (time in, out, number, good and bad,,,) and then only one column for each of the stations (Stn;Set time;Time;Time down;Time up;Temp;Amp H with the corresponding values.
The columns in the new format should be:
Date;Date in;Date out;Flightbar number;Process number;Order number1;Part number1;Total area4;Following alarm exist:;Stn;Set time;Time;Time down;Time up;Temp;Amp H;

For each flight number i will have x rows according to the number of x stations used.

I thought I know PQ a bit but I am confused how to so this problem. I attached the example CSV file for a better understanding.

Any thoughts from here?

thanks a lot

[AliGW]

Please provide the workbook containing your attempt at pivoting the data using PQ so that we can troubleshoot.

[hansolu]

Hi,
I don't even know how to start this task, thats my issue, sorry

[AliGW]

OK, well, sorry, I'm not going to do this for you. The data is a complete mess and it would take hours for me to first work out what's what and then work out how to process it. Don't have the time for that - sorry.

[hansolu]

Sorry, missunderstandig. I dont want to be lazy and let work done by others. I just don't know how to start

I played a bit and created a template which is more easy to understand.
Basically the first columns are the basic data (column 1-30), column 31 (Stn) to column 37 (Amp H) are the values which are repeating many times for each row.
I transformed the first samples manually into one sheet (Final Output Wish) to show it more clear.

I don't know how to split the columns from 31 to the end to the get desired output like shown.

I would appreciate some ideas how I should start.

thanks a lot

[AliGW]

Thank you for this - the fact that you have made an effort makes a big difference. I'll have another look, but I suspect it will be one of the forum members who is better at PQ than I am that will solve this.

[Bo_Ry]

Please try

let
    Source = Excel.CurrentWorkbook(){[Name="CSV_Import"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Quantity1", "Quantity bad1", "Rejected1", "Total area1", "Order number2", "Part number2", "Quantity2", "Quantity bad2", "Rejected1_1", "Total area2", "Order number3", "Part number3", "Quantity3", "Quantity bad3", "Rejected1_2", "Total area3", "Order number4", "Part number4", "Quantity4", "Quantity bad4", "Rejected1_3", "Total area4", "Following alarm exist:"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "Date in", "Date out", "Flightbar number", "Process number", "Order number1", "Part number1"}, "Attribute", "Value"),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted Text Before Delimiter", "Index", 0, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Date", "Date in", "Date out", "Flightbar number", "Process number", "Order number1", "Part number1", "Attribute", "Value"}),
    #"Integer-Divided Column" = Table.TransformColumns(#"Reordered Columns", {{"Index", each Number.IntegerDivide(_, 7), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index", ""}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Time down", type time}, {"Time up", type time}})
in
    #"Changed Type"

[hansolu]

Hello,

thanks a lot, nearly perfect. just the timing is mixed up, i will try to explain, just need some time to figure out how to describe

[hansolu]

Hi,
i found the issue.

when doing the unpivot step, PQ skip the fields with a NULL inside, this will screw up the order at the Integer-Divided step.
So I inserted a REPLACE null with "" to ensure every column is considered in unpivot. Then the order is fine and everything is working properly

One thing i need to look into for myself is the Step Integer-Divided Column and the Pivot afterwards. I do not really understand this yet, but time will tell hopefully.

Thanks a lot for your help.

[Bo_Ry]

Integer-Divided is the number to keep the same number to stay in the same row after Pivot column.

There are neater ways to do this using Table.Group

let
    Source = Excel.CurrentWorkbook(){[Name="CSV_Import"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Quantity1", "Quantity bad1", "Rejected1", "Total area1", "Order number2", "Part number2", "Quantity2", "Quantity bad2", "Rejected1_1", "Total area2", "Order number3", "Part number3", "Quantity3", "Quantity bad3", "Rejected1_2", "Total area3", "Order number4", "Part number4", "Quantity4", "Quantity bad4", "Rejected1_3", "Total area4", "Following alarm exist:"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Date", "Date in", "Date out", "Flightbar number", "Process number", "Order number1", "Part number1"}, "A", "V"),
    Extracted = Table.TransformColumns(#"Unpivoted Other Columns", {{"A", each Text.BeforeDelimiter(_, "_"), type text}}),
    Group = Table.Group(Extracted,"A",{"T", each Table.Pivot(_, List.Distinct([A]), "A", "V") },GroupKind.Local,(x,y)=> Number.From(y ="Stn")),
    Combine = Table.Combine(Group[T])
in
    Combine

[hansolu]

Hi

thanks for the second option, i will try to understand. I guess the grouping function is better fit for my purpose,

Thanks for the help, really appreciate.