Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
RaudMees
Regular Visitor

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?

schema pq.png

RaudMees

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

View solution in original post

13 REPLIES 13
RaudMees
Regular Visitor

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

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.

 

Edited. Now it works I hope. 

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

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

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.

 

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. 

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

For example with White:

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

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"

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

Cheers!

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors