• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Supplier Name and stock item is in the same colomn. Powerquery solution please

s2310

New Member
Hello,

Please see the attached excel.

The supplier name and the stock item are in the same column.

Please provide powerquery solution.

I have also shown the desired result format.

Please check if it is possilbe.

Regards,
 

Attachments

  • Purchasereport query.xlsx
    17.6 KB · Views: 6

s2310

You wrote Please provide powerquery solution.
... then why did You opened Your thread in Ask an Excel Question-forum?
In this time, this thread is moved to correct Forum.
 
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Date] <> null then [Particulars] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Date", "Custom", "Particulars", "Quantity", "Rate", "Total Value", "Vch No."}),
    #"Filled Down1" = Table.FillDown(#"Reordered Columns",{"Vch No.", "Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Total Value] <> "Purchase"))
in
    #"Filtered Rows"
 

Attachments

  • Purchasereport query.xlsx
    21.6 KB · Views: 8
Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Date] <> null then [Particulars] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Date", "Custom", "Particulars", "Quantity", "Rate", "Total Value", "Vch No."}),
    #"Filled Down1" = Table.FillDown(#"Reordered Columns",{"Vch No.", "Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Total Value] <> "Purchase"))
in
    #"Filtered Rows"
Thankyou sir,

I understand the logical flow used now..

Much much appreciated
 
Back
Top