New Columns with different/specific Requirements- Power BI

[shansakhi]

Hello Friends,
I am trying to create two columns as highlighted in "green" below.
Columns highligted in "yellow" are the available data in table.

Column "Routing" - This is derived based on columns "Tkt No", "Seg" and "Route"
Column "Value (Actual)" - This is derived based on column "Value $" - In data entire ticket value is repeated for eash segment so that only on seg value to be considered.

P.S. - the sequecce of "Seg" may not be in order. we may have mixed data.

Book1
	H	I	J	K	L	M
2	Ttk No	Seg	Route	Value ($)	Routing	Value (Actual)
3	1234	1	BOM-DEL	105	BOM-DEL-SXR-SXR-DEL-BOM	105
4	1234	2	DEL-SXR	105	BOM-DEL-SXR-SXR-DEL-BOM	0
5	1234	3	SXR-DEL	105	BOM-DEL-SXR-SXR-DEL-BOM	0
6	1234	4	DEL-BOM	105	BOM-DEL-SXR-SXR-DEL-BOM	0
7
8	4567	1	BOM-DEL	55	BOM-DEL	55
9
10	3315	1	BOM-DEL	75	BOM-DEL-BOM	75
11	3315	2	DEL-BOM	75	BOM-DEL-BOM	0
12
13	3672	1	BOM-DEL	95	BOM-DEL-SXR-IXJ-DEL-BOM	95
14	3672	2	DEL-SXR	95	BOM-DEL-SXR-IXJ-DEL-BOM	0
15	3672	3	IXJ-DEL	95	BOM-DEL-SXR-IXJ-DEL-BOM	0
16	3672	4	DEL-BOM	95	BOM-DEL-SXR-IXJ-DEL-BOM	0
17
18	3232	1	BOM-DEL	85	BOM-DEL-CCU	85
19	3232	2	DEL-CCU	85	BOM-DEL-CCU	0
Sheet2

Regards,
SC

 

[alex78]

Hi @shansakhi,

if I have well understood.

let
A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
B = Table.ColumnNames(A),
C = (x,y,z)=> Table.InsertRows(x, y, Table.ToRecords(#table(z, {List.Repeat({null}, List.Count(z))}))),
D = Table.Group(C(A,0,B), B{0}, {"x", each
 [
    a = Table.Sort(Table.Skip(_), B{1}),
    b = (x,y)=> List.Repeat({x},Table.RowCount(a) + y),
    c = Text.Combine(List.RemoveNulls(List.Combine(List.Accumulate( List.Transform(Table.Column(a,B{2}), each Text.Split(_,"-")),
        {{null,null}}, (s,c)=> s & {if List.Last(s){1} = c{0} then {null,c{1}} else c}))),"-"),
    d = Table.ToColumns(a) & {b(c, 0)} & {{Table.Column(a,B{3}){0}} & b(0,-1)},
    e = Table.FromColumns(d, B & {"Routing", "Value(Actual)"}),
    f = C(e, Table.RowCount(e), Table.ColumnNames(e))
 ] [f]}, 0, (x,y)=> Byte.From(y = null))[x],
E = Table.RemoveLastN(Table.Combine(D))
in E

Regards,