Get Data in Power Query

[sambit]

Sir,

Kindly help to resolve the below issue.

I have a data range B5:J24, as per my requirement I have solved by formula.

But, I want in by power query?

Is it possible my requirement which I get by formula is solved by power query?

If yes, then please guide how to do.

Example data is attached for your kind information.

 

[Monty]

Hello Sambit


Certainly! Power Query is a powerful tool in Excel that allows you to transform and manipulate data. To replicate your formula-based solution using Power Query:

1. Select any cell within your data range B5:J24.
2. Go to the "Data" tab in the Excel ribbon.
3. Click on "Get Data" or "From Table/Range" (depending on your Excel version).
4. This will open the Power Query Editor.
5. In the Power Query Editor, you can apply various transformations and calculations to your data using the available options.

If you provide more details about your specific formula or requirements, I can guide you through the steps to replicate it using Power Query. Feel free to share the formula or any specific transformations you want to apply, and I'll assist you accordingly.

 

[GraH - Guido]

Hi @sambit

I'm a bit confused by your formulas. In the example below, I've filtered on a a single vendor code and there is only 1 invoice number. So what's the logic behind the Total Invoice No as per vendor? It says 2. Why?


And I do not see the need for repeating the value column Time_Care: Gross.

This Power Query does already what you need, except the counter I do not get.

// data
let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Add_Index = Table.AddIndexColumn(Source, "StartPos", 1, 1, Int64.Type),
    Group_all_inv_vendor = Table.Group(Add_Index, {"Inv No", "Vendor Code"}, {{"All", each _, type table [Inv No=number, #"Net Weight."=number, In Date=datetime, In Time=number, Out Date=datetime, Out Time=number, Mode=text, Vendor Code=number, #"Time_Tare:Gross"=number]}}),
    Add_index_all = Table.AddColumn(Group_all_inv_vendor, "Add Count vendor code", each Table.AddIndexColumn([All], "Count vendor code",1,1, Int64.Type)),
    Keep_indexed_table = Table.SelectColumns(Add_index_all,{"Add Count vendor code"}),
    Expand_indexed_table = Table.ExpandTableColumn(Keep_indexed_table, "Add Count vendor code", {"Inv No", "Net Weight.", "In Date", "In Time", "Out Date", "Out Time", "Mode", "Vendor Code", "Time_Tare:Gross", "StartPos", "Count vendor code"}, {"Inv No", "Net Weight.", "In Date", "In Time", "Out Date", "Out Time", "Mode", "Vendor Code", "Time_Tare:Gross", "StartPos", "Count vendor code"}),
    Resort_original = Table.Sort(Expand_indexed_table,{{"StartPos", Order.Ascending}}),
    Add_actual_time = Table.AddColumn(Resort_original, "Actual time", each if [Count vendor code] = 1 then [#"Time_Tare:Gross"] else null, type duration),
    Remove_StartPos_col = Table.RemoveColumns(Add_actual_time,{"StartPos"}),
    Set_types = Table.TransformColumnTypes(Remove_StartPos_col,{{"Inv No", Int64.Type}, {"Net Weight.", type number}, {"In Date", type datetime}, {"In Time", type time}, {"Out Date", type datetime}, {"Out Time", type time}, {"Mode", type text}, {"Vendor Code", Int64.Type}, {"Time_Tare:Gross", type duration}, {"Count vendor code", Int64.Type}, {"Actual time", type duration}})
in
    Set_types

 

[Monty]

Hi @sambit

I'm a bit confused by your formulas. In the example below, I've filtered on a a single vendor code and there is only 1 invoice number. So what's the logic behind the Total Invoice No as per vendor? It says 2. Why?
View attachment 86331

And I do not see the need for repeating the value column Time_Care: Gross.

This Power Query does already what you need, except the counter I do not get.

// data
let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Add_Index = Table.AddIndexColumn(Source, "StartPos", 1, 1, Int64.Type),
    Group_all_inv_vendor = Table.Group(Add_Index, {"Inv No", "Vendor Code"}, {{"All", each _, type table [Inv No=number, #"Net Weight."=number, In Date=datetime, In Time=number, Out Date=datetime, Out Time=number, Mode=text, Vendor Code=number, #"Time_Tare:Gross"=number]}}),
    Add_index_all = Table.AddColumn(Group_all_inv_vendor, "Add Count vendor code", each Table.AddIndexColumn([All], "Count vendor code",1,1, Int64.Type)),
    Keep_indexed_table = Table.SelectColumns(Add_index_all,{"Add Count vendor code"}),
    Expand_indexed_table = Table.ExpandTableColumn(Keep_indexed_table, "Add Count vendor code", {"Inv No", "Net Weight.", "In Date", "In Time", "Out Date", "Out Time", "Mode", "Vendor Code", "Time_Tare:Gross", "StartPos", "Count vendor code"}, {"Inv No", "Net Weight.", "In Date", "In Time", "Out Date", "Out Time", "Mode", "Vendor Code", "Time_Tare:Gross", "StartPos", "Count vendor code"}),
    Resort_original = Table.Sort(Expand_indexed_table,{{"StartPos", Order.Ascending}}),
    Add_actual_time = Table.AddColumn(Resort_original, "Actual time", each if [Count vendor code] = 1 then [#"Time_Tare:Gross"] else null, type duration),
    Remove_StartPos_col = Table.RemoveColumns(Add_actual_time,{"StartPos"}),
    Set_types = Table.TransformColumnTypes(Remove_StartPos_col,{{"Inv No", Int64.Type}, {"Net Weight.", type number}, {"In Date", type datetime}, {"In Time", type time}, {"Out Date", type datetime}, {"Out Time", type time}, {"Mode", type text}, {"Vendor Code", Int64.Type}, {"Time_Tare:Gross", type duration}, {"Count vendor code", Int64.Type}, {"Actual time", type

 

[sambit]

Hi @sambit

I'm a bit confused by your formulas. In the example below, I've filtered on a a single vendor code and there is only 1 invoice number. So what's the logic behind the Total Invoice No as per vendor? It says 2. Why?
View attachment 86331

And I do not see the need for repeating the value column Time_Care: Gross.

This Power Query does already what you need, except the counter I do not get.

// data
let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Add_Index = Table.AddIndexColumn(Source, "StartPos", 1, 1, Int64.Type),
    Group_all_inv_vendor = Table.Group(Add_Index, {"Inv No", "Vendor Code"}, {{"All", each _, type table [Inv No=number, #"Net Weight."=number, In Date=datetime, In Time=number, Out Date=datetime, Out Time=number, Mode=text, Vendor Code=number, #"Time_Tare:Gross"=number]}}),
    Add_index_all = Table.AddColumn(Group_all_inv_vendor, "Add Count vendor code", each Table.AddIndexColumn([All], "Count vendor code",1,1, Int64.Type)),
    Keep_indexed_table = Table.SelectColumns(Add_index_all,{"Add Count vendor code"}),
    Expand_indexed_table = Table.ExpandTableColumn(Keep_indexed_table, "Add Count vendor code", {"Inv No", "Net Weight.", "In Date", "In Time", "Out Date", "Out Time", "Mode", "Vendor Code", "Time_Tare:Gross", "StartPos", "Count vendor code"}, {"Inv No", "Net Weight.", "In Date", "In Time", "Out Date", "Out Time", "Mode", "Vendor Code", "Time_Tare:Gross", "StartPos", "Count vendor code"}),
    Resort_original = Table.Sort(Expand_indexed_table,{{"StartPos", Order.Ascending}}),
    Add_actual_time = Table.AddColumn(Resort_original, "Actual time", each if [Count vendor code] = 1 then [#"Time_Tare:Gross"] else null, type duration),
    Remove_StartPos_col = Table.RemoveColumns(Add_actual_time,{"StartPos"}),
    Set_types = Table.TransformColumnTypes(Remove_StartPos_col,{{"Inv No", Int64.Type}, {"Net Weight.", type number}, {"In Date", type datetime}, {"In Time", type time}, {"Out Date", type datetime}, {"Out Time", type time}, {"Mode", type text}, {"Vendor Code", Int64.Type}, {"Time_Tare:Gross", type duration}, {"Count vendor code", Int64.Type}, {"Actual time", type duration}})
in
    Set_types

sir,
2 Nos of vendor code against invoice no - 24136.
if you filter invoice no - 24136, then you get 2 nos of vendor code i.e, 2023000648 & 2023000923

 

[GraH - Guido]

So it's the number of vendors per order number.
Thanks for clarifying.
I might have a look later today still.

 

[GraH - Guido]

You can add a second grouping, like below.

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Add_Index = Table.AddIndexColumn(Source, "StartPos", 1, 1, Int64.Type),
    Group_all_inv_vendor = Table.Group(Add_Index, {"Inv No", "Vendor Code"}, {{"All", each _, type table [Inv No=number, #"Net Weight."=number, In Date=datetime, In Time=number, Out Date=datetime, Out Time=number, Mode=text, Vendor Code=number, #"Time_Tare:Gross"=number]}}),
    Add_index_all = Table.AddColumn(Group_all_inv_vendor, "Add Count vendor code", each Table.AddIndexColumn([All], "Count vendor code",1,1, Int64.Type)),
    Group_on_invoice = Table.Group(Add_index_all, {"Inv No"}, {{"Total Invoice No as per vendor", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Inv No=number, Vendor Code=number, All=table, Add Count vendor code=table]}}),
    Expand_all_indexed = Table.ExpandTableColumn(Group_on_invoice, "All", {"Add Count vendor code"}, {"Add Count vendor code"}),
    Keep_indexed_table = Table.SelectColumns(Expand_all_indexed,{"Total Invoice No as per vendor", "Add Count vendor code"}),
    Move_all_to_first_col = Table.ReorderColumns(Keep_indexed_table,List.Reverse(Table.ColumnNames((Keep_indexed_table)))),
    Expand_indexed_table = Table.ExpandTableColumn(Move_all_to_first_col, "Add Count vendor code", {"Inv No", "Net Weight.", "In Date", "In Time", "Out Date", "Out Time", "Mode", "Vendor Code", "Time_Tare:Gross", "StartPos", "Count vendor code"}, {"Inv No", "Net Weight.", "In Date", "In Time", "Out Date", "Out Time", "Mode", "Vendor Code", "Time_Tare:Gross", "StartPos", "Count vendor code"}),
    Resort_original = Table.Sort(Expand_indexed_table,{{"StartPos", Order.Ascending}}),
    Add_actual_time = Table.AddColumn(Resort_original, "Actual time", each if [Count vendor code] = 1 then [#"Time_Tare:Gross"] else null, type duration),
    Remove_StartPos_col = Table.RemoveColumns(Add_actual_time,{"StartPos"}),
    Set_types = Table.TransformColumnTypes(Remove_StartPos_col,{{"Inv No", Int64.Type}, {"Net Weight.", type number}, {"In Date", type datetime}, {"In Time", type time}, {"Out Date", type datetime}, {"Out Time", type time}, {"Mode", type text}, {"Vendor Code", Int64.Type}, {"Time_Tare:Gross", type duration}, {"Count vendor code", Int64.Type}, {"Actual time", type duration}})
in
    Set_types

 

[sambit]

GraH - Guido Sir,
Thank you very much.
Can you please guide how to do step by step process.

 

[GraH - Guido]

Can you please guide how to do step by step process.

How proficient are you with M-code, Sambit?
My steps are explicitly names, so that guides you step by step. However to do the same, you need to understand at least how to tweak the code.
I must admit, that since a few years, I do not spend a lot of time any more on the forum. I simply do not have the time to explain much in great details. If you have precise questions I can do my best to answer a correctly as possible.

Hope you understand.
Guido