"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]

Sorry for not correct view, I meant 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

Many thnks!

 

[IlyaK]

some problem with spaces

 

[theBardd]

Headings aside, which I assume are in multiple columns, is the data all in one column?

 

[IlyaK]

Hi, theBardd!
Yes, in result I want to get characteristic in multiple columns with names of it.
In input table I have 2 columns:characteristic name and value. Count of characteristic names and values is fluent I know how to parse it in regular excel, but input file is huge (acc transaction) and it will be work slowly. (

 

[theBardd]

Ok, I had a crack at it.

This is what I did I steps:
- created a basic query from the original table, splitting the first column and filling the operation down
- created another query with just the weight data
- created another query with just the diameter data
- created another query with just the planes data
- created a final query where I took the basic query, removed the weights, diameter and planes rows, then merged the 3 sets in from their individual queries.

Here is the code.

BasicQuery

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    remove.Extra = Table.RemoveColumns(Source,{"Characteristic Name", "Sum"}),
    rename.Name = Table.RenameColumns(remove.Extra,{{"Name", "Sum"}}),
    split.Operation = Table.SplitColumn(rename.Name,"Operation",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Operation.1", "Operation.2", "Operation.3", "Operation.4"}),
    filldown.Operation = Table.FillDown(Table.AddColumn(split.Operation, "Operation", each if Text.Start([Operation.1],3) = "No." then [Operation.1] else null),{"Operation"}),
    remov.ColorLabel = Table.RemoveColumns(filldown.Operation,{"Operation.3"})
in
    remov.ColorLabel

IsolateWeight

let
    Source = BasicQuery,
    filter.Key = Table.SelectRows(Source, each ([Operation.1] = "Weight")),
    just.KeySumn = Table.RemoveColumns(filter.Key,{"Operation.1","Operation.2", "Operation.4"})
in
    just.KeySumn

IsolateDiameter

let
    Source = BasicQuery,
    filter.Key = Table.SelectRows(Source, each ([Operation.1] = "Diameter")),
    just.KeySumn = Table.RemoveColumns(filter.Key,{"Operation.1","Operation.2", "Operation.4"})
in
    just.KeySumn

IsolatePlanes

let
    Source = BasicQuery,
    filter.Key = Table.SelectRows(Source, each ([Operation.1] = "Planes")),
    just.KeySumn = Table.RemoveColumns(filter.Key,{"Operation.1","Operation.2", "Operation.4"})
in
    just.KeySumn

FinalResult

let
    Source = BasicQuery,
    merge.Weight = Table.ExpandTableColumn(
                        Table.NestedJoin(Source,
                                         {"Operation"}, IsolateWeight,
                                         {"Operation"}, "NewColumn",
                                         JoinKind.LeftOuter), 
                        "NewColumn", 
                        {"Sum"}, 
                        {"Weight"}),
    merge.Diameter = Table.ExpandTableColumn(
                        Table.NestedJoin(merge.Weight,
                                         {"Operation"}, IsolateDiameter,
                                         {"Operation"}, "NewColumn",
                                         JoinKind.LeftOuter), 
                        "NewColumn", 
                        {"Sum"}, 
                        {"Diameter"}),
    merge.Planes = Table.ExpandTableColumn(
                        Table.NestedJoin(merge.Diameter,
                                         {"Operation"}, IsolatePlanes,
                                         {"Operation"}, "NewColumn",
                                         JoinKind.LeftOuter), 
                        "NewColumn", 
                        {"Sum"}, 
                        {"Planes"}),
    filter.Operation = Table.SelectRows(merge.Planes, each Text.StartsWith([Operation.1], "No.")),
    final.Headings = Table.RenameColumns(filter.Operation,{{"Operation.2", "Name"}, {"Operation.4", "Color"}}),
    remove.OldOperation = Table.RemoveColumns(final.Headings,{"Operation.1"}),
    final.Order = Table.ReorderColumns(remove.OldOperation,{"Operation", "Name", "Sum", "Color", "Weight", "Diameter", "Planes"})
in
    final.Order

 

[theBardd]

Oh, don't forget to set the load of each query to Connection only, except the final result query.

 

[IlyaK]

Hi theBardd! Thanks a lot for your answer! Amount of code little bit scary me I'll try it at Monday. By a glimpse view of code I catch that quantity of characteristic_name should be const. and defined at moment of creation of query? It suit to my current sitiution, but may be it's possible to make it dynamicaly? Thanks one more.

 

[theBardd]

Don't be alarmed by the code, it is quite straight-forward. Three are essentially the same, just different filter values (possibly could be a function). The first justs splits the initial column so as to give us something to work on, and the last merges the first with the other 3, and toidies up the results.

I guess that you could setup a list of the values, and pull that in. Will it be a set number of values or could that vary also?

 

[anvg]

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

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),
    removed = Table.RemoveColumns(joined,{"Index", "Characteristic name"}),
    return = Table.RenameColumns(removed,{{"Characteristics", "Color"}})
in
    return

Regards, Andrey