Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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.
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?
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