I have a horizontal table that looks like below:
This is a kind of an inventory table, and a unique key can be made by combining the part# and the code.
Each part# has a block of 7 rows including a blank row which is the last row of the block. (The red box is one block). I would like to extract only the QTY tied to the part#. Since not all cells of the Part No column are entered, it is hard to get the QTY of the part#. I do not want to copy the part#s manually in the PartNo column because the table has a lot of parts.
I would like a table like this:
What I tried are:
- First I needed to get how many parts are in the table. -> It has 200 parts.
- Power Query: Filter the category column by QTY -> NG since I cannot see the part#.
- Concatenate part# and code to make a unique key, and then use index match. -> the number of parts does not match.
What is the best way to extract what I need with this kind of table??
***From comment
This is the final table I am trying to create.
CodePudding user response：
Try this. Bring the data into powerquery using data ... from table/range.
Add column, custom column with formula
= if [Part No]<> null and [shipping info]=null then [Part No] else null
Add column, custom column with formula
= if [Part No]<> null and [shipping info]=null then [code] else null
Click select those two columns and right click Fill down
Use the drop down atop the Category column to filter for value [x] QTY
Click select the original part number column, the original code column and the shipping info and date columns, and right click remove them
This may be enough for you, and to end here, do File ... close and load. However, if there are duplicate rows that need to be combined and added together then click select the category, part and code columns, right click and unpivot other columns. Then click select the attribute column and transform .. pivot column ... using the Value as the value column, and in advanced options choose [x] sum
sample full code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "MasterPart", each if [Part No]<> null and [shipping info]=null then [Part No] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "MasterCode", each if [Part No]<> null and [shipping info]=null then [code] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"MasterPart", "MasterCode"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([category] = "QTY")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Part No", "code", "shipping info", "date"}),
// you may just be able to stop here unless rows need to be added together
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"category", "MasterPart", "MasterCode"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Sum)
in #"Pivoted Column"
