converting table by distributing values

[LoryMenCR]

Dear All,

I have a table that looks like this (TABLE1):

Code	Group	Value	Date
1	abc	1000	15/06/2024
1	xyz	1500	30/06/2024
2	abc	2000	10/07/2024
3	xyz	3000	15/07/2024
4	abc	1000	01/06/2024
4	abc	500	02/09/2024
5	xyz	100	03/09/2024

What I want to achieve is: for certain codes I want to distribute the value based on percentages within the month.

I have another table that control percentages and it looks like this (TABLE2):

Start Code	Start Code Description	End Code	%
2	Fruit	1	40
2	Fruit	4	60
3	Grocery	1	20
3	Grocery	4	30
3	Grocery	5	50

This means that Code number 2 values will always be splitted throught the end codes (1 and 4) with certain percentages.

What I want to achieve through PowerQuery is converting TABLE1 as follows:

Code	Group	Value	Date
1	abc	1000	15/06/2024
1	xyz	1500	30/06/2024
1	FRUIT	800	10/07/2024
1	GROCERY	600	15/07/2024
4	abc	1000	01/06/2024
4	FRUIT	1200	10/07/2024
4	GROCERY	900	15/07/2024
4	abc	500	02/09/2024
5	GROCERY	1500	15/07/2024
5	xyz	100	03/09/2024

The transformed TABLE1 is presenting distributed values as per TABLE2 while original codes that have been distributed are no longer shown.

I'm struggling with this a lot 😞

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc5BDoAgDATAv/RMwtKCxrcQDuon1NeL1FDx0h4mu23OFMjRuu11BgDPSh6TZ3Ck4tSP82rQXDA49zy/+eqzufS8WP/H4/8+wtBvrufBHotxsveUxbjc", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Group = _t, Value = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", type number}, {"Date", type date}},"en-GB"),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Code"}, Table2, {"Start Code"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Start Code Description", "End Code", "%"}, {"Start Code Description", "End Code", "%"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table2",each [Code],each if [End Code]=null then [Code] else [End Code],Replacer.ReplaceValue,{"Code"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each [Group],each if [End Code]=null then [Group] else [Start Code Description],Replacer.ReplaceValue,{"Group"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Value],each if [End Code]=null then [Value] else [Value]/100*[#"%"],Replacer.ReplaceValue,{"Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value2",{{"Value", Currency.Type}, {"Code", type text}, {"Group", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Code", "Group", "Value", "Date"})
in
    #"Removed Other Columns"

see attached