Power Query (Get&Transform)

[hailstorm]

Hello there

A few weeks ago i asked a question and a nice member of this website told me about this tool in excel, it solved my problem, but since i cannot send private message, here i'm again.

I have this very long sheet filled with more than 160.000 lines of data ( 2.923 products ), so i what do i need? I'd like to have the number of days at the end of each month and the number of sells of all products, example.

my sheet have 03 columns, a=date,01/01/2020 * b=product,chocolate * c=quantity sold,01 .... the entire sheet is registered with 01 sell per line. So what i tried was this.

= Table.Group(#"Tipo Alterado", {"product"}, {{"Count", each List.Sum([quantity sold]), type number}, {"january", each Table.RowCount(_), type number}, {"february", each Table.RowCount(_), type number}, {"march", each Table.RowCount(_), type number}})

The counting repeat to all the new columns that it's created, it's only sum all the 01.

Is there a way to Power Query detect each month and sum in a different column?

Thanks.

 

[sandy666]

any example via xl2bb or, if there is no classified data, shared workbook ? (with expected result)

 

[hailstorm]

 

[sandy666]

is that what you want?

based on your not representative, poor example (incomplete picture!)

 

[hailstorm]

is that what you want?

based on your not representative, poor example (incomplete picture!)

View attachment 3084

Yes, i want to know how many each product sold in each month.

 

[sandy666]

for incomplete example above...

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"product", type text}, {"quantity sold", Int64.Type}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([date]), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Month Name", {"product", "Month Name"}, {{"Q sold", each List.Sum([quantity sold]), type number}, {"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Month", each List.Distinct(Table.Column([Count],"Month Name"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Month", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "QS", each List.Distinct(Table.Column([Count],"quantity sold"))),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"QS", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Month]), "Month", "QS", List.Count)
in
    #"Pivoted Column"

so adapt to your needs

 

[hailstorm]

for incomplete example above...

// Table2
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"product", type text}, {"quantity sold", Int64.Type}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([date]), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Month Name", {"product", "Month Name"}, {{"Q sold", each List.Sum([quantity sold]), type number}, {"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Month", each List.Distinct(Table.Column([Count],"Month Name"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Month", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "QS", each List.Distinct(Table.Column([Count],"quantity sold"))),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"QS", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Month]), "Month", "QS", List.Count)
in
    #"Pivoted Column"

so adapt to your needs

Thanks, again.
It worked.

 

[sandy666]

You are welcome
Thanks for the feedback