add table column dynamically from csv

[SvenJ]

Good evening everyone,

i have the problem, that i need to add columns to an existing table dynamically to calculate sums. The header is always the same, but the amount of rows and the number "#BWA-Zeile" is different.

csv 1
#BWA-Zeile	Zeilenbeschriftung	Zeile von	Zeile bis
1051	Product A	1020	1045
1080	Product B	1051	1060
1092	Total	1080	1090
1280	Costs	1100	1260
1300	Revenue	1092	1280
1320	Some costs	1310	1312
1330	Some incoing	1322	1324
1345	Test	1300	1320
1345	Teste 2	1330	1340
1380	final amount	1345	1355

csv 2
#BWA-Zeile	Zeilenbeschriftung	Zeile von	Zeile bis
1021	Amount	1017	1019
1027	Product A	1023	1025
1040	Product B	1017	1023
1042	Product C	1018	1024
1043	Product D	1019	1025
1051	Total	1040	1043
1072	Costs	1061	1071
1075	Revenue	1051	1072
1219	some costs	1207	1211
1299	Test	1243	1267
1345	check	1075	1219
1380	final amount	1345	1355

At the moment i´m adding and calculating the "rows" like this:

  #"S1051" = Table.AddColumn(#"Gefilterte Zeilen1", "1051", each if (([BWA Zeile] >= 1020) and ([BWA Zeile] <= 1045)) then[Umsatz Values] else 0),
    #"S1080" = Table.AddColumn(S1051, "1080",  each [1051] -  (if ([BWA Zeile] >= 1051) and ([BWA Zeile] <= 1060)  then [Umsatz Values] else 0)),
    #"S1092" = Table.AddColumn(#"S1080", "1092", each [1080] +  (if ([BWA Zeile] >= 1080) and ([BWA Zeile] <= 1090)  then [Umsatz Values] else 0)),
    #"S1280" = Table.AddColumn(#"S1092", "1280", each if (([BWA Zeile] >= 1100) and ([BWA Zeile] <= 1260)) then [Umsatz Values] else 0),
    #"S1300" = Table.AddColumn(#"S1280", "1300", each [1092] - [1280]),
    #"S1320" = Table.AddColumn(#"S1300", "1320", each  if (([BWA Zeile] >= 1310) and ([BWA Zeile] <= 1312)) then [Umsatz Values] else 0),
    #"S1330" = Table.AddColumn(#"S1320", "1330", each if (([BWA Zeile] >= 1320) and ([BWA Zeile] <= 1324)) then[Umsatz Values] else 0),
    #"S1345" = Table.AddColumn(#"S1330", "1345", each [1300] - [1320] + [1330]),
    #"S1380" = Table.AddColumn(#"S1345", "1380", each [1345] - (if ([BWA Zeile] = 1355) then[Umsatz Values] else 0)),
    #"Geänderter Typ6" = Table.TransformColumnTypes(#"S1380",{{"Umsatz Values", type number},{"1080", type number}, {"1092", type number}, {"1280", type number}, {"1300", type number},{"1320", type number}, {"1330", type number}, {"1345", type number},{"1380", type number},{"1051", type number}}),
    

It means that i need "#BWA Zeile" as the column name and "Zeile von" to "Zeile bis" as a calculation. Any help would be great

[SvenJ]

@AlexisOlson You,re right, that´s the calculation of this, but how can i get it dynamically into the advanced power editor? Some columns have different names, one table has x (amount) rows, the other one has y (amount) rows.

[AlexisOlson]

I don't understand what you're trying to do well enough to say. What are all these extra columns for and what are the rules that define how you want them to behave? Why do some have addition/subtraction and others don't?

[AlexisOlson]

I don't quite understand the logic behind all these columns but it seems like you're comparing the first column to the last two a lot and this could be simplified by replacing e.g.

if (([BWA Zeile] >= 1020) and ([BWA Zeile] <= 1045)) then[Umsatz Values] else 0

with column references instead of those hard-coded numbers:

if ([BWA Zeile] >= [Zeile von]) and ([BWA Zeile] <= [Zeile bis]) then [Umsatz Values] else 0