Collecting variable data from cells

[RaudMees]

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

[RaudMees]

I will try again 🙂 
Link to file is here: https://1drv.ms/x/s!AoS5q2ShsQhBgmMPGH8S6Jk-yiGl?e=dHD3gZ  
Table inside with data and on another sheet sample how I like to get this Edge data. 

RaudMees

[ronrsnfld]

The link does not work.  Use something like OneDrive, DropBox or similar to share your file.

Or post your sample data as text and your expected results (from that data) as a screenshot here.

[RaudMees]

Edited. Now it works I hope. 

[ronrsnfld]

The link works, but I'm not understanding how you are deriving your Results from the data you are presenting.

 For example, a method that will properly sum White  will not work for the other types

[RaudMees]

This is manually created sample from data to result. I need to automate this function somehow. 

[ronrsnfld]

I understand that.  

But without knowing the logic you are using to create these results from your data, no one can automate the solution.  And I am not understanding the logic you are using.

[RaudMees]

I am trying to explain it better.
To calculate total length for Fenix 0720 edgebandings, read values from edge1 column, take length for Fenix 0720 from length column and then multiplication with Quantity column (I). Row by row. Then same with columns edge2, edge3 and edge 4, finally SUM those 4 values from each column. 
Then same with U899 and White. 

Edge1...Edge4 materials are:

1. Fenix 0720

2. U899

3. White

....

199. Empty cell

200. Black

Column with Type - it means when there is 'edge', then calculate only that row. If it's needed for faster calculation. 

[ronrsnfld]

When I do that, I get very different values from what you show for Results

[RaudMees]

For example with White:

length ((685+685)*2) + ((309+309)*2)= 3976

[ronrsnfld · Accepted solution]

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"

[RaudMees]

Thx a lot mate! This works very well. I made several tests with different settings and all good! 

Cheers!

[RaudMees]

Sorry, for me total length means length + width 🙂 it's furniture particular board edgebandings calculation. 

I will try to test it. Thx so far. 

[edhans]

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.