Forum Discussion

RaudMees's avatar
RaudMees
Regular Visitor
4 years ago
Solved

Collecting variable data from cells

Hello

I am in trouble to get correct data from shown table below. I need total length for edge materials. Bad thing is - material name is variable, there might be up to 200 different names. Empty cells also in use, like one part is without edge coverings. One column is not shown here, but there is fixed info for edge. When no data in edge1...edge4, then this cell in that column is empty. 

Main target is to collect all different edge names with length and then total sum for each edge type. I can add those names into "code" to check availability. Any ideas?

RaudMees

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    4 years ago

    Ah.  You wrote to calculate length.  But you really want length and width.

     

    Try this (explanation later if needed)

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"INDEX3D", Int64.Type}, {"length", Int64.Type}, {"edge1", type text}, {"edge2", type text}, {"width", Int64.Type}, {"edge3", type text}, {"edge4", type text}, {"Type", type text}, {"Q.", Int64.Type}}),
    #"Length x Quantity" = Table.AddColumn(#"Changed Type", "Length x Quantity", each [length] * [#"Q."], Int64.Type),
    #"Width x Quantity" = Table.AddColumn(#"Length x Quantity","Width x Quantity", each [width] * [#"Q."], Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Width x Quantity", each ([Type] = "edge")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"length", "width", "Q.", "INDEX3D", "Type"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Length x Quantity", "Width x Quantity"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Lengths", each if [Attribute]="edge1" or [Attribute]="edge2" then [Length x Quantity] else [Width x Quantity]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Length x Quantity", "Width x Quantity", "Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Value"}, {{"Total Length", each List.Sum([Lengths]), type number}})
in
    #"Grouped Rows"

13 Replies