Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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"
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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting