Power Query to Extract Unique Records

L

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,214
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm looking for a Power Query solution to extract unique records based on the Date, ID, Amount, and Food columns. For ID 123 the customer ordered milk on 2/5/2024 and received it on 2/7/2024. The same customer ordered eggs on 2/5/2025 but it has not been received (since there is no paired record). The first date is always the ordered date and the second date is always the received date. The data is sorted by ID , then Food, then by Date so it'll show if a customer ordered and/or received the item based on the Date column.

To get the unique records I need to use the ID, Amount, and Food column but I don't know how to put the respective dates for each record into its own column. Can any Power Query experts help transform this data to my desired result?

This is my raw data:

DateIDRegionAmountGroupFood
2/5/2024​
123​
East
$100​
1​
Milk
2/7/2024​
123​
West
$100​
2​
Milk
2/5/2024​
123​
East
$100​
3​
Eggs
2/5/2024​
888​
West
$50​
5​
Cookies
2/6/2024​
888​
East
$50​
3​
Cookies
2/12/2024​
777​
West
$100​
5​
Apple
2/10/2024​
123​
East
$100​
3​
Milk
2/11/2024​
123​
West
$100​
5​
Milk

This is what the result should look like:

IDAmountFoodOrdered DateReceived Date
123100Milk
2/5/2024​
2/7/2024​
123100Eggs
2/5/2024​
Not Received
88850Cookies
2/5/2024​
2/6/2024​
777100Apple
2/12/2024​
Not Received
123100Milk
2/10/2024​
2/11/2024​
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Assuming your table is called Table5 in your Excel workbook, you can do this:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}, {"Region", type text}, {"Amount", Int64.Type}, {"Group", Int64.Type}, {"Food", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "IndexSort", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ID_Amount_Food", each Number.ToText([ID]) & Number.ToText([Amount]) & [Food]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ID_Amount_Food"}, {{"Count", each _, type table [Date=nullable datetime, ID=nullable number, Region=nullable text, Amount=nullable number, Group=nullable number, Food=nullable text, ID_Amount_Food=text]}}),
    #"Added Table Index" = Table.AddColumn(#"Grouped Rows", "New Table", each Table.AddIndexColumn([Count], "Index", 1, 1, Int64.Type)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Table Index",{"Count"}),
    #"Expanded New Table" = Table.ExpandTableColumn(#"Removed Columns", "New Table", {"Date", "ID", "Region", "Amount", "Group", "Food", "IndexSort", "ID_Amount_Food", "Index"}, {"Date", "ID", "Region", "Amount", "Group", "Food", "IndexSort", "ID_Amount_Food.1", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded New Table",{{"IndexSort", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom", each if Number.IsEven([Index]) then "Received Date" else "Ordered Date"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"ID_Amount_Food", "Index", "ID_Amount_Food.1", "Group", "Region"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"ID", "Amount", "Food"}, {{"All", each _, type table [Date=date, ID=number, Region=text, Amount=number, Food=text, Custom=text]}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows1",{"ID", "Amount", "Food"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns2", "All", {"Date", "ID", "Amount", "Food", "Custom","IndexSort"}, {"Date", "ID", "Amount", "Food", "Custom","IndexSort"}),
    #"Grouped Rows2" = Table.Group(#"Expanded All", {"Food"}, {{"Count", each _, type table [Date=nullable date, ID=nullable number, Amount=nullable number, Food=nullable text, Custom=nullable text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.Group([Count], {"Custom"}, {{"Count", each _, type table [Date=nullable date, ID=nullable number, Amount=nullable number, Food=nullable text, Custom=nullable text]}})))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Ordered Date", "Received Date"}, {"Ordered Date", "Received Date"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Ordered_Date", each [Ordered Date][Date]),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Ordered Date"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns3", "Received_Date", each try [Received Date][Date] otherwise null),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom4",{"Received Date"}),
    #"Added Custom5" = Table.AddColumn(#"Removed Columns4", "Final", each try Table.FromColumns({[Ordered_Date],[Received_Date]}) otherwise #table(null, {{[Ordered_Date]{0}, null}})),
    #"Removed Columns5" = Table.RemoveColumns(#"Added Custom5",{"Ordered_Date", "Received_Date"}),
    #"Expanded Final" = Table.ExpandTableColumn(#"Removed Columns5", "Final", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Expanded Final", "Count", {"ID", "Amount","IndexSort"}, {"ID", "Amount","IndexSort"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Count",{{"IndexSort", Order.Ascending}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Sorted Rows1",{"IndexSort"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns6"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Column1", "Ordered Date"}, {"Column2", "Received Date"}})
in
    #"Renamed Columns"


There is definitely an easier way of doing this, I was doing this while running another task though.
 
Upvote 0
The whole idea is to group records, split them by 2 (order and delivery) and transform each pair into single record.
You don't have order_id so nobody knows when 123's Milk order dated 02/05/24 was actually delivered - 02/10 or 02/11. Please also take into account that Table.Group may shuffle data. You may add index column and sort to account for this.
Power Query: 
let
    Source = your_data,
    fx = (tbl) => tbl{0}[[ID], [Amount], [Food], [Date]] & [Received Date = tbl{1}?[Date]?],
    group = Table.Group(
        Source,
        {"ID", "Amount", "Food"},
        {"x", (x) => List.Transform(Table.Split(x, 2), fx)}
    ),
    z = Table.FromRecords(List.Combine(group[x]))
in
    z
 
Upvote 0
ExcelToDAX said:
Assuming your table is called Table5 in your Excel workbook, you can do this:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}, {"Region", type text}, {"Amount", Int64.Type}, {"Group", Int64.Type}, {"Food", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "IndexSort", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ID_Amount_Food", each Number.ToText([ID]) & Number.ToText([Amount]) & [Food]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ID_Amount_Food"}, {{"Count", each _, type table [Date=nullable datetime, ID=nullable number, Region=nullable text, Amount=nullable number, Group=nullable number, Food=nullable text, ID_Amount_Food=text]}}),
    #"Added Table Index" = Table.AddColumn(#"Grouped Rows", "New Table", each Table.AddIndexColumn([Count], "Index", 1, 1, Int64.Type)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Table Index",{"Count"}),
    #"Expanded New Table" = Table.ExpandTableColumn(#"Removed Columns", "New Table", {"Date", "ID", "Region", "Amount", "Group", "Food", "IndexSort", "ID_Amount_Food", "Index"}, {"Date", "ID", "Region", "Amount", "Group", "Food", "IndexSort", "ID_Amount_Food.1", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded New Table",{{"IndexSort", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom", each if Number.IsEven([Index]) then "Received Date" else "Ordered Date"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"ID_Amount_Food", "Index", "ID_Amount_Food.1", "Group", "Region"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"ID", "Amount", "Food"}, {{"All", each _, type table [Date=date, ID=number, Region=text, Amount=number, Food=text, Custom=text]}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows1",{"ID", "Amount", "Food"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns2", "All", {"Date", "ID", "Amount", "Food", "Custom","IndexSort"}, {"Date", "ID", "Amount", "Food", "Custom","IndexSort"}),
    #"Grouped Rows2" = Table.Group(#"Expanded All", {"Food"}, {{"Count", each _, type table [Date=nullable date, ID=nullable number, Amount=nullable number, Food=nullable text, Custom=nullable text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.Group([Count], {"Custom"}, {{"Count", each _, type table [Date=nullable date, ID=nullable number, Amount=nullable number, Food=nullable text, Custom=nullable text]}})))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Ordered Date", "Received Date"}, {"Ordered Date", "Received Date"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Ordered_Date", each [Ordered Date][Date]),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Ordered Date"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns3", "Received_Date", each try [Received Date][Date] otherwise null),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom4",{"Received Date"}),
    #"Added Custom5" = Table.AddColumn(#"Removed Columns4", "Final", each try Table.FromColumns({[Ordered_Date],[Received_Date]}) otherwise #table(null, {{[Ordered_Date]{0}, null}})),
    #"Removed Columns5" = Table.RemoveColumns(#"Added Custom5",{"Ordered_Date", "Received_Date"}),
    #"Expanded Final" = Table.ExpandTableColumn(#"Removed Columns5", "Final", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Expanded Final", "Count", {"ID", "Amount","IndexSort"}, {"ID", "Amount","IndexSort"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Count",{{"IndexSort", Order.Ascending}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Sorted Rows1",{"IndexSort"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns6"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Column1", "Ordered Date"}, {"Column2", "Received Date"}})
in
    #"Renamed Columns"


There is definitely an easier way of doing this, I was doing this while running another task though.
Click to expand...
That works but the steps look convoluted, I'm sure there is an easier way with less steps. If you have another method, I would appreciate else I will stick with what you provided
 
Upvote 0
AlienSx said:
The whole idea is to group records, split them by 2 (order and delivery) and transform each pair into single record.
You don't have order_id so nobody knows when 123's Milk order dated 02/05/24 was actually delivered - 02/10 or 02/11. Please also take into account that Table.Group may shuffle data. You may add index column and sort to account for this.
Power Query: 
let
    Source = your_data,
    fx = (tbl) => tbl{0}[[ID], [Amount], [Food], [Date]] & [Received Date = tbl{1}?[Date]?],
    group = Table.Group(
        Source,
        {"ID", "Amount", "Food"},
        {"x", (x) => List.Transform(Table.Split(x, 2), fx)}
    ),
    z = Table.FromRecords(List.Combine(group[x]))
in
    z
Click to expand...
As mentioned in my OP the order date is always before the received date. When ID 123 ordered on milk on 2/5/24 then the received date is 2/7/24. When ID 123 ordered milk again on 2/10/24 the received date is 2/11/24. The data is sorted so you will always have the order date and/or received date. In some instances like ID 123 ordered eggs on 2/5/24 but they never received it (since there is no paired ID/Date/Food) therefore the received date is null as shown in the results table in my OP.
 
Upvote 0
ExcelToDAX said:
Assuming your table is called Table5 in your Excel workbook, you can do this:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}, {"Region", type text}, {"Amount", Int64.Type}, {"Group", Int64.Type}, {"Food", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "IndexSort", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ID_Amount_Food", each Number.ToText([ID]) & Number.ToText([Amount]) & [Food]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ID_Amount_Food"}, {{"Count", each _, type table [Date=nullable datetime, ID=nullable number, Region=nullable text, Amount=nullable number, Group=nullable number, Food=nullable text, ID_Amount_Food=text]}}),
    #"Added Table Index" = Table.AddColumn(#"Grouped Rows", "New Table", each Table.AddIndexColumn([Count], "Index", 1, 1, Int64.Type)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Table Index",{"Count"}),
    #"Expanded New Table" = Table.ExpandTableColumn(#"Removed Columns", "New Table", {"Date", "ID", "Region", "Amount", "Group", "Food", "IndexSort", "ID_Amount_Food", "Index"}, {"Date", "ID", "Region", "Amount", "Group", "Food", "IndexSort", "ID_Amount_Food.1", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded New Table",{{"IndexSort", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom", each if Number.IsEven([Index]) then "Received Date" else "Ordered Date"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"ID_Amount_Food", "Index", "ID_Amount_Food.1", "Group", "Region"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"ID", "Amount", "Food"}, {{"All", each _, type table [Date=date, ID=number, Region=text, Amount=number, Food=text, Custom=text]}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows1",{"ID", "Amount", "Food"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns2", "All", {"Date", "ID", "Amount", "Food", "Custom","IndexSort"}, {"Date", "ID", "Amount", "Food", "Custom","IndexSort"}),
    #"Grouped Rows2" = Table.Group(#"Expanded All", {"Food"}, {{"Count", each _, type table [Date=nullable date, ID=nullable number, Amount=nullable number, Food=nullable text, Custom=nullable text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.Group([Count], {"Custom"}, {{"Count", each _, type table [Date=nullable date, ID=nullable number, Amount=nullable number, Food=nullable text, Custom=nullable text]}})))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Ordered Date", "Received Date"}, {"Ordered Date", "Received Date"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Ordered_Date", each [Ordered Date][Date]),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Ordered Date"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns3", "Received_Date", each try [Received Date][Date] otherwise null),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom4",{"Received Date"}),
    #"Added Custom5" = Table.AddColumn(#"Removed Columns4", "Final", each try Table.FromColumns({[Ordered_Date],[Received_Date]}) otherwise #table(null, {{[Ordered_Date]{0}, null}})),
    #"Removed Columns5" = Table.RemoveColumns(#"Added Custom5",{"Ordered_Date", "Received_Date"}),
    #"Expanded Final" = Table.ExpandTableColumn(#"Removed Columns5", "Final", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Expanded Final", "Count", {"ID", "Amount","IndexSort"}, {"ID", "Amount","IndexSort"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Count",{{"IndexSort", Order.Ascending}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Sorted Rows1",{"IndexSort"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns6"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Column1", "Ordered Date"}, {"Column2", "Received Date"}})
in
    #"Renamed Columns"


There is definitely an easier way of doing this, I was doing this while running another task though.
Click to expand...
If I change the amount for the top two rows for ID 123 to $1000.75 the output result creates separate distinct line that is not sorted properly. Even when I tried to change the amount to 1000 (without a decimal) it incorrectly gave the wrong output. I tried changing the data type in the 'change type' step in Power Query from whole number to decimal number but that didn't work either.

Please help fix this. Thanks
 
Upvote 0
Hi @legalhustler,

Try - your original table is named Table1 in my proposal,

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    ColName = {Table.ColumnNames(Source)} & {{"ID", "Amount", "Food"}} & {{"Ordered Date", "Received Date"}},
    #"Grouped Rows" = Table.Combine(Table.Group(Source, ColName{0}{5}, {{"Count", each
            let
            x = Table.ToRows(Table.SelectColumns(_,ColName{1})){0} & Table.Column(_, ColName{0}{0}),
            y = List.Count(x),
            z = List.Union({ColName{1}, ColName{2}})
            in  Table.FromRows({if y <> List.Count(z) then List.InsertRange(x,y, {"Not Received"}) else x}, z)
                }},0)[Count])
in
#"Grouped Rows"

Regards,
 
Upvote 0
alex78 said:
Hi @legalhustler,

Try - your original table is named Table1 in my proposal,

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    ColName = {Table.ColumnNames(Source)} & {{"ID", "Amount", "Food"}} & {{"Ordered Date", "Received Date"}},
    #"Grouped Rows" = Table.Combine(Table.Group(Source, ColName{0}{5}, {{"Count", each
            let
            x = Table.ToRows(Table.SelectColumns(_,ColName{1})){0} & Table.Column(_, ColName{0}{0}),
            y = List.Count(x),
            z = List.Union({ColName{1}, ColName{2}})
            in  Table.FromRows({if y <> List.Count(z) then List.InsertRange(x,y, {"Not Received"}) else x}, z)
                }},0)[Count])
in
#"Grouped Rows"

Regards,
Click to expand...
Worked well! If I wanted to add another column which parts of the steps do I need to update? What does this mean, ColName{0}{5},? If possible, can it be done using the Power Query interface so I understand it better?
 
Upvote 0
As you should already know:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Excel Forum

Extracting Unique Records
www.myonlinetraininghub.com www.myonlinetraininghub.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
ExcelToDAX said:
Assuming your table is called Table5 in your Excel workbook, you can do this:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"ID", Int64.Type}, {"Region", type text}, {"Amount", Int64.Type}, {"Group", Int64.Type}, {"Food", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "IndexSort", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "ID_Amount_Food", each Number.ToText([ID]) & Number.ToText([Amount]) & [Food]),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ID_Amount_Food"}, {{"Count", each _, type table [Date=nullable datetime, ID=nullable number, Region=nullable text, Amount=nullable number, Group=nullable number, Food=nullable text, ID_Amount_Food=text]}}),
    #"Added Table Index" = Table.AddColumn(#"Grouped Rows", "New Table", each Table.AddIndexColumn([Count], "Index", 1, 1, Int64.Type)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Table Index",{"Count"}),
    #"Expanded New Table" = Table.ExpandTableColumn(#"Removed Columns", "New Table", {"Date", "ID", "Region", "Amount", "Group", "Food", "IndexSort", "ID_Amount_Food", "Index"}, {"Date", "ID", "Region", "Amount", "Group", "Food", "IndexSort", "ID_Amount_Food.1", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded New Table",{{"IndexSort", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Custom", each if Number.IsEven([Index]) then "Received Date" else "Ordered Date"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"ID_Amount_Food", "Index", "ID_Amount_Food.1", "Group", "Region"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns1", {"ID", "Amount", "Food"}, {{"All", each _, type table [Date=date, ID=number, Region=text, Amount=number, Food=text, Custom=text]}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows1",{"ID", "Amount", "Food"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns2", "All", {"Date", "ID", "Amount", "Food", "Custom","IndexSort"}, {"Date", "ID", "Amount", "Food", "Custom","IndexSort"}),
    #"Grouped Rows2" = Table.Group(#"Expanded All", {"Food"}, {{"Count", each _, type table [Date=nullable date, ID=nullable number, Amount=nullable number, Food=nullable text, Custom=nullable text]}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows2", "Custom", each Table.PromoteHeaders(Table.Transpose(Table.Group([Count], {"Custom"}, {{"Count", each _, type table [Date=nullable date, ID=nullable number, Amount=nullable number, Food=nullable text, Custom=nullable text]}})))),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"Ordered Date", "Received Date"}, {"Ordered Date", "Received Date"}),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom", "Ordered_Date", each [Ordered Date][Date]),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"Ordered Date"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns3", "Received_Date", each try [Received Date][Date] otherwise null),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom4",{"Received Date"}),
    #"Added Custom5" = Table.AddColumn(#"Removed Columns4", "Final", each try Table.FromColumns({[Ordered_Date],[Received_Date]}) otherwise #table(null, {{[Ordered_Date]{0}, null}})),
    #"Removed Columns5" = Table.RemoveColumns(#"Added Custom5",{"Ordered_Date", "Received_Date"}),
    #"Expanded Final" = Table.ExpandTableColumn(#"Removed Columns5", "Final", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Expanded Final", "Count", {"ID", "Amount","IndexSort"}, {"ID", "Amount","IndexSort"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Count",{{"IndexSort", Order.Ascending}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Sorted Rows1",{"IndexSort"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns6"),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Duplicates",{{"Column1", "Ordered Date"}, {"Column2", "Received Date"}})
in
    #"Renamed Columns"


There is definitely an easier way of doing this, I was doing this while running another task though.
Click to expand...

IDRegionAmountGroupFoodOrdered DateReceived Date
123East1001Milk2/5/20242/7/2024
123South1001Eggs2/5/2024Not Received
888North505Cookies2/5/20242/6/2024
777West1006Apple2/12/2024Not Received
123South1003Milk2/10/20242/11/2024

RoryA said:
As you should already know:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Excel Forum

Extracting Unique Records
www.myonlinetraininghub.com www.myonlinetraininghub.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
My apologies, I wasn't aware of the cross posting rule. That was the only other place I posted to. I got the solution I was looking for. It's very simple and easy to understand. Here it is for reference:

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"ID", "Amount", "Food"}, {{"Group", each _, type table [Date=nullable date, ID=text, Region=text, Amount=number, Group=number, Food=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.SelectColumns([Group], "Date")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Custom])),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.1",{{"Column1", "Ordered"}, {"Column2", "Received"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Group", "Custom"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ID", type text}, {"Amount", Currency.Type}, {"Food", type text}, {"Ordered", type date}, {"Received", type date}})
in
    #"Changed Type"
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

L
  • Solved
Power Query to Determine Consecutive and Non-Consecutive Dates
2 3
Replies
26
Views
1K
legalhustler
L
R
  • Question Question
Convert Excel fx to a Power Query fx
Replies
1
Views
275
RattlingCarp3048
R
J
  • Solved
Power Query: Selection of columns with date headers
Replies
9
Views
633
GraH
GraH
M
  • Question Question
Power Query to find name matches between two spreadsheets
Replies
2
Views
286
MrSamExcel
M
A
  • Question Question
Transforming a Table in Excel Power Query: Grouping and Splitting Rows Based on Unique Order IDs
Replies
3
Views
144
alex78
A

Forum statistics

Threads
1,223,880
Messages
6,175,153
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top