• 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.

Combine 2 different datasets for forecasting

T

TKDLANCE

New Member
I'm very new to Power Pivot and could use some help.

I have 2 different files with parts and dates that will be used for forecasting. We are trying to combine the 2 data sets into Power Query so that it will create a table that has the part number on the column, the month on the row, and the data in the middle.

Normally, I would just create a pivot table but I'm trying to create something that can just be a paste the data, and it will do the calculation for them.

Where I'm getting stuck is in dataset 1 data has the dates are individual days and in the column, and the total for each part number. Dataset 2 has months in a row and how I need the data to look.

Should I be using Power Query, Power Pivot, or something else?
In the dataset there will be other parts added to the dataset.
What is the best way to combine that data so that it looks like the dataset 2?
If anyone can give me a tutorial on how to make the data in Dataset1 look like Dataset2 what would be a great help.
 

Attachments

at beginners level, and not optimal, but get's you going.
Code: 
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Vendor's Part #", type text}, {"Eng. Change Level", type text}, {"Schedule QTY", type any}, {"Planned Quantity", type any}, {"Ship Date", type date}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Ship Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Ship Date] <> null)),
    #"Calculated Start of Month" = Table.TransformColumns(#"Filtered Rows",{{"Ship Date", Date.StartOfMonth, type date}}),
    #"Inserted Addition" = Table.AddColumn(#"Calculated Start of Month", "Qty", each [Schedule QTY] + [Planned Quantity], type number),
    #"Grouped Rows" = Table.Group(#"Inserted Addition", {"Vendor's Part #", "Ship Date"}, {{"Det", each _, type table [#"Vendor's Part #"=nullable text, Eng. Change Level=nullable text, Schedule QTY=number, Planned Quantity=number, Ship Date=date, Qty=number]}, {"Qty", each List.Sum([Qty]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Ship Date", "Month"}, {"Vendor's Part #", "Parts"}})
in
    #"Renamed Columns"

Code: 
// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rest P/N", type any}, {"Store P/N", type text}, {"25/aug", Int64.Type}, {"25/sep", Int64.Type}, {"25/okt", Int64.Type}, {"25/nov", Int64.Type}, {"25/dec", Int64.Type}, {"26/jan", Int64.Type}, {"26/feb", Int64.Type}, {"26/mrt", Int64.Type}, {"26/apr", Int64.Type}, {"26/mei", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Rest P/N", "Store P/N"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Attribute.1", type text}}, "nl-BE"),{"Attribute.2", "Attribute.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Month"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Month", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Rest P/N", "Parts"}, {"Value", "Qty"}})
in
    #"Renamed Columns"

Code: 
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Vendor's Part #", type text}, {"Eng. Change Level", type text}, {"Schedule QTY", type any}, {"Planned Quantity", type any}, {"Ship Date", type date}}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Ship Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Errors", each ([Ship Date] <> null)),
    #"Calculated Start of Month" = Table.TransformColumns(#"Filtered Rows",{{"Ship Date", Date.StartOfMonth, type date}}),
    #"Inserted Addition" = Table.AddColumn(#"Calculated Start of Month", "Qty", each [Schedule QTY] + [Planned Quantity], type number),
    #"Grouped Rows" = Table.Group(#"Inserted Addition", {"Vendor's Part #", "Ship Date"}, {{"Det", each _, type table [#"Vendor's Part #"=nullable text, Eng. Change Level=nullable text, Schedule QTY=number, Planned Quantity=number, Ship Date=date, Qty=number]}, {"Qty", each List.Sum([Qty]), type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Ship Date", "Month"}, {"Vendor's Part #", "Parts"}})
in
    #"Renamed Columns"

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rest P/N", type any}, {"Store P/N", type text}, {"25/aug", Int64.Type}, {"25/sep", Int64.Type}, {"25/okt", Int64.Type}, {"25/nov", Int64.Type}, {"25/dec", Int64.Type}, {"26/jan", Int64.Type}, {"26/feb", Int64.Type}, {"26/mrt", Int64.Type}, {"26/apr", Int64.Type}, {"26/mei", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Rest P/N", "Store P/N"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Attribute.1", type text}}, "nl-BE"),{"Attribute.2", "Attribute.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Month"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"Month", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Rest P/N", "Parts"}, {"Value", "Qty"}})
in
    #"Renamed Columns"

// Append1
let
    Source = Table.Combine({Table1, Table2}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Det", "Store P/N"}),
    #"Inserted Month Name" = Table.AddColumn(#"Removed Columns", "Month Name", each Date.MonthName([Month], "en-US"), type text),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
    #"Extracted Year" = Table.TransformColumns(#"Extracted First Characters",{{"Month", Date.Year, type text}}),
    #"Extracted Last Characters" = Table.TransformColumns(#"Extracted Year", {{"Month", each Text.End(Text.From(_), 2), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted Last Characters",{"Month Name", "Month"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Month"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Month]), "Month", "Qty", List.Sum)
in
    #"Pivoted Column"

I personally would not do the pivot thing in PQ, but in Excel. Removes the need of a few annoying steps.
 

Attachments

You must log in or register to reply here.
Back
Top