"Normalization" of bad structured report

[IlyaK]

Hi everyone!

Here I have not clearly structured report, I have to convert in pivotable view. Like this:

Operation | Name | Characteristic name | Characteristics | Sum |
No.1 Ball Color red 5
Weight 2.5
Diameter 10
No.2 Cube Color green 2
Planes 6
No.3 Rectangle Color blue 1

In output table I want to see:

Operation| Name |Sum|Color |Weight| Diameter| Planes |
No.1 Ball 5 red 2.5 10
No.2 Cube 2 green 6
No.3 Rectangle 1 blue

I want to solve this problem by Power Query, but haven't enough skills..
Have some thoughts about spliting input table in numerous tables (by quantity of characteristics names) and join them by "Operation", but can't handle with inexplicit number of characteristic names..

Thanks a lot and sorry for my English

 

[IlyaK]

Andrey, good evening! It's really shorter and look great! Look like that it's what I need.. Will try it next week.
I want to learn "M" as you know it...

Offtopic: What the weather in Piter?

 

[IlyaK]

Hi, theBardd! In real task characteristic_name is fixed and characteristic_value is varyable.. But it's interesting try to build full dynamic converter.

 

[theBardd]

Hi
It is a interesting task. My version is a bit shorter.

It is better Andrey, but I think you missed a bit.

let
    source = Excel.CurrentWorkbook(){[Name="DataTable"]}[Content],
    base = Table.SelectRows(source,each Text.StartsWith([Operation],"No.")),
    addIndex = Table.AddColumn(source,"Index",each if Text.StartsWith([Operation],"No.") then [Operation] else null),
    fillIndex = Table.FillDown(addIndex,{"Index"}),
    prePivot = Table.SelectRows(fillIndex,each not Text.StartsWith([Operation],"No."))[[Index],[Operation],[Name]],
    pivoted = Table.Pivot(prePivot,{"Weight","Diameter","Planes"},"Operation","Name"),
    joined = Table.Join(base,{"Operation"},pivoted,{"Index"},JoinKind.LeftOuter),
    split.Operation = Table.SplitColumn(joined,"Operation",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Operation.1", "Operation.2", "Operation.3", "Operation.4"}),
    removed = Table.RemoveColumns(split.Operation,{"Operation.3", "Index", "Characteristic name", "Characteristics", "Sum"}),
    return = Table.RenameColumns(removed,{{"Operation.1", "Operation"},{"Name", "Sum"},{"Operation.2", "Name"},{"Operation.4", "Color"}})
in
    return

 

[theBardd]

oops, and the order

let
    source = Excel.CurrentWorkbook(){[Name="DataTable"]}[Content],
    base = Table.SelectRows(source,each Text.StartsWith([Operation],"No.")),
    addIndex = Table.AddColumn(source,"Index",each if Text.StartsWith([Operation],"No.") then [Operation] else null),
    fillIndex = Table.FillDown(addIndex,{"Index"}),
    prePivot = Table.SelectRows(fillIndex,each not Text.StartsWith([Operation],"No."))[[Index],[Operation],[Name]],
    pivoted = Table.Pivot(prePivot,{"Weight","Diameter","Planes"},"Operation","Name"),
    joined = Table.Join(base,{"Operation"},pivoted,{"Index"},JoinKind.LeftOuter),
    split.Operation = Table.SplitColumn(joined,"Operation",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Operation.1", "Operation.2", "Operation.3", "Operation.4"}),
    removed = Table.RemoveColumns(split.Operation,{"Operation.3", "Index", "Characteristic name", "Characteristics", "Sum"}),
    reordered = Table.ReorderColumns(removed,{"Operation.1", "Name", "Operation.2", "Operation.4", "Weight", "Diameter", "Planes"}),
    return = Table.RenameColumns(reordered,{{"Operation.1", "Operation"},{"Name", "Sum"},{"Operation.2", "Name"},{"Operation.4", "Color"}})
in
    return

 

[anvg]

Hi

I want to learn "M" as you know it...

Half a year solving PQ tasks and you will able to free create like code.

What the weather in Piter?

It is a lot warmer than in Sakha where I lived before.

but I think you missed a bit.

I am sorry. But I not found any information that weight 2.5 is a single cell value. I supposed that they are two values of Operation and Name columns. Indeed if it is the single cell then my code contains a mistake and it is needed to add code for splitting it into two columns as you have written above. Thank you for your remark.

oops, and the order

I think that IlyaK is clever and can make needed a order of columns himself.
Regards, Andrey.

 

[theBardd]

I am sorry. But I not found any information that weight 2.5 is a single cell value. I supposed that they are two values of Operation and Name columns. Indeed if it is the single cell then my code contains a mistake and it is needed to add code for splitting it into two columns as you have written above.

So you don't think that the fact that the OP had a separate cell for the Weight in the output was any sort of clue. Must be the cold that chills those eastern souls.

 

[anvg]

Hi

Must be the cold that chills those eastern souls.

Is it a compliment or sensure? I am a begginer in Enlish last 15 years.
Regards, Andrey.