Forum Discussion
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 ce...
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"
For example with White:
length ((685+685)*2) + ((309+309)*2)= 3976
ronrsnfld
Super User
Super UserAh. 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"