Adding customn column based on dynamic reference column value

[Anonymous]

Hi Guys,

Here is my source data look like, 
As Is

so the column skuid's value is mixed up between real sku code and reference code,
i want to remove reference code and move qty value align with sku and plant .
example
row 1-> sku id = 74556 , reference code = 45xxxx & 45xxxx , plant = 9030, total qty 5200
i want to remove reference code, so the final result is
sku id, plant, totalQty
To Be

Here is the pbi file sample :

sample file

i've found example from google but the total row for  reference is same and not change dynamically, so has not solved yet.
Your help will be so much appreciated!
Thank You

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can apply a FillDown on your Plant-Column and then group it by the Plant, applying a sum-function to your quantity column

Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjcxNTVT0lGyNDA2AFJKsTrRShUVIJaOkpGZgQFUAEMISZ8pTF9iUjJElTmGIkNLmKLy1KLilDSIOlOoOqAAUBgiBrYgFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Skuid = _t, Plant = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Skuid", type text}, {"Plant", Int64.Type}, {"Quantity", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Plant"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Plant"}, {{"Quantity", each List.Sum([Quantity]), type number}})
in
    #"Grouped Rows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Crystal_YW · Accepted solution]

hello, I've solved the issue as follows:

let
    Source = Excel.Workbook(File.Contents("C:\Desktop\Test\P1\t1\sample data.xlsx"), null, true),
    source_Sheet = Source{[Item="source",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(source_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Skuid", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Skuid", Text.Trim, type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 0, 1),
    AddSP = Table.AddColumn(#"Added Index","StartPosition", each if Text.Length([Skuid])<>10 and not Text.StartsWith([Skuid],"45") then [Index] else null),
    AddEP = Table.AddColumn(AddSP,"EndPosition", each List.Select(List.Range(AddSP[StartPosition],_[Index]+1), each _<>null){0}),
    Addallup = Table.AddColumn(AddEP,"TotalQty",each try List.Sum(List.Range(AddEP[Qty],[StartPosition]+1,[EndPosition]-[StartPosition]-1)) otherwise null),
    #"Filtered Rows" = Table.SelectRows(Addallup, each ([TotalQty] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Skuid", "Plant", "TotalQty"})
in
    #"Removed Other Columns"

[Anonymous]

Hi @Crystal_YW ,

Thank you so much,

your advice help me to solved this issue!

Regards,

Haniya

[Jimmy801 · Accepted solution]

Hello @Anonymous 

you can apply a FillDown on your Plant-Column and then group it by the Plant, applying a sum-function to your quantity column

Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjcxNTVT0lGyNDA2AFJKsTrRShUVIJaOkpGZgQFUAEMISZ8pTF9iUjJElTmGIkNLmKLy1KLilDSIOlOoOqAAUBgiBrYgFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Skuid = _t, Plant = _t, Quantity = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Skuid", type text}, {"Plant", Int64.Type}, {"Quantity", Int64.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Plant"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Plant"}, {{"Quantity", each List.Sum([Quantity]), type number}})
in
    #"Grouped Rows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi @Jimmy801 ,
Thank you so much!
I've tried your advice and give little modification so sku_id can include and it solved!

Regards,
Haniya