Power Query Unqiue Row Formatting With Sum

Question

Trying to figure out if this is even possible as LLMs have failed to answer this question.

I have a report from our companie's ERP that exports as a CSV. This is a basic inventory report that looks like this:

Condition	Location	Part Number	Code	Count
Healthy	SHELF125	CircutCard702	FEP-275	1
Healthy	SHELF125	CircutCard702	FEP-354	1
Healthy	FLOOR290	Engine200HP	Oshkosh1003	1
Healthy	SHELF029	Radio78800	50455B	1
Broken	FLOOR990	Transmission9	Nissan30120	1
Broken	FLOOR790	Radio78800	89004A	1
Broken	FLOOR790	Radio78800	75605B	1

The export list each item and their location in the factory floor and since each item has a code assigned to it as a serial number, the count for each item is essentially always 1.

I want to know if Power Query can import this and transform it to look like the following in Excel:

Condition	Location	Part Number	Total / Code
Healthy	SHELF125	CircutCard702	2
Healthy	SHELF125		FEP-354
Healthy	SHELF125		FEP-354
Healthy	FLOOR290	Engine200HP	1
Healthy	FLOOR290		Oshkosh1003
Healthy	SHELF029	Radio78800	1
Healthy	SHELF029		50455B
Broken	FLOOR990	Transmission9	1
Broken	FLOOR990		Nissan30120
Broken	FLOOR790	Radio78800	2
Broken	FLOOR790		89004A
Broken	FLOOR790		75605B

Essentially, I want the first row to contain the sum of the quantity on that location. Then the following rows will omit the part number and just contain the unique codes for each of those items on that location. This makes it easier for our staff to read and they seem dead set in this formatting.

I got halfway there by grouping in Power Query but so far the LLMs have been unable to figure out how to get it to this final layout.

Is this something that can only be half done with Power Query and then I will have to move onto a OnChange macro to finish the layout?

Any help would be appreciated. Thank you.

kb2136 · Accepted Answer · 2025-10-29 14:11:48Z

The key is to first add a helper Index column.
Then create a duplicate of the query.

In the original query, rename the Code column (Count is redundant) and wipe out the Part Number:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Code", "Total/Code"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Count"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns",each [Part Number],null,Replacer.ReplaceValue,{"Part Number"})
in
    #"Replaced Value"

In the duplicate query, group by the three first columns. Get the sum of Count and a minimum of Index – you need this to sort the end result. The aggregate columns should be named approprietly:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Condition", "Location", "Part Number"}, {{"Index", each List.Min([Index]), type number}, {"Total/Code", each List.Sum([Count]), type number}})
in
    #"Grouped Rows"

In the end, simply append and sort:

let
    Source = Table.Combine({Table1, #"Table1 (2)"}),
    #"Sorted Rows" = Table.Sort(Source,{{"Index", Order.Ascending}, {"Part Number", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Ron Rosenfeld · Accepted Answer · 2025-10-30 11:30:54Z

1

And here's another method that seems to execute more rapidly:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Condition", type text}, {"Location", type text}, {"Part Number", type text}, {"Code", type text}, {"Count", Int64.Type}}),

//Don't need the Count column
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Count"}),
    
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Location", "Part Number"}, {
        {"Count/Code", each {Text.From(Table.RowCount(_))} & [Code], type {text}}}),
    #"Expanded Count/Code" = Table.ExpandListColumn(#"Grouped Rows", "Count/Code"),
    
    #"Blank Part Number" = Table.ReplaceValue(
        #"Expanded Count/Code",
        each [Part Number],
        each [#"Count/Code"],
        (x,y,z) as nullable text => if (try Number.From(z))[HasError] = false then y else null,
        {"Part Number"}
    )
in
    #"Blank Part Number"

Note: Since you are actually importing from a *.csv file, some of the above code is unnecessary:

let

//Note Columns=4 since we do not need the fifth column (Count)    

    Source = Csv.Document(File.Contents("pathname\filename.csv"),
        [Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Location", "Part Number"}, {
        {"Count/Code", each {Text.From(Table.RowCount(_))} & [Code], type {text}}}),
    #"Expanded Count/Code" = Table.ExpandListColumn(#"Grouped Rows", "Count/Code"),
    
    #"Blank Part Number" = Table.ReplaceValue(
        #"Expanded Count/Code",
        each [Part Number],
        each [#"Count/Code"],
        (x,y,z) as nullable text => if (try Number.From(z))[HasError] = false then y else null,
        {"Part Number"}
    )
in
    #"Blank Part Number"

edited Oct 30 at 11:30

answered Oct 30 at 0:33

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

2 Comments

Spectral Instance Oct 30 at 3:00

#"Changed Type" could be shortened to Table.TransformColumns(Source,{"Count", Int64.From}, Text.From) or, really, to Table.TransformColumns(Source,{}, Text.From) since the Count column is being dropped

Ron Rosenfeld Oct 30 at 11:32

Since his data source is a CSV file and not an Excel table, there's no need for the #"Changed Type" line at all. Merely import the four columns only and they will all get typed as text, within the Source line. (See my edit). If the data source is an Excel table, then you can just omit the type specification for Count or, better, delete it before #"Changed Type" step.

horseyride · Accepted Answer · 2025-10-29 17:53:06Z

An all in one query method

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source , {"Condition", "Location", "Part Number"}, {
        {"All", each let 
        a = Table.AddColumn(_,"PartNumber2", each null)
        in Table.InsertRows(a, 0, {[Condition=a[Condition]{0}, Location=a[Location]{0},  Part Number=null,Code=Text.From(Table.RowCount(a)), Count=null,PartNumber2=a[Part Number]{0}]})
     , type table [Condition=text, Location=text,  Part Number=text, Code=text,Count=number,PartNumber2=text]  }}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"All"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Other Columns", "All", {"Condition", "Location","PartNumber2", "Code" }, {"Condition", "Location", "Part Number","Total / Code" })
    in  #"Expanded All"

Collectives™ on Stack Overflow

Power Query Unqiue Row Formatting With Sum

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related