Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table which has a column called "Konto" and a cell with the value of 8421 in this table.
Now i want to search in another table between two columns (Konto von - Konto bis) and give back the value #BWA-Zeile.
How can i do that in Power Query? I know how to do it as a calculated column, but this value is not in Power Query. Can someone help me?
Solved! Go to Solution.
Hier:
each Table.SelectRows(BWA
greift er auf die existierende BWA Tabelle zu. Anbei ein sample PBIX.
Und an welcher Stelle greift er auf welche Tabelle zu?
Many thanks for your help! Vielen lieben Dank!
@lbendlin This is not a problem - i can fix it to 8599 and start the other line with 8600
That would be better. Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.
This is the table, where i have the values, which i need called "BWA" - i need the value BWA_Zeile, if the value is between "Konto von" and "Konto bis"
And i need the value in this table called "Import"
Kannst Du das bitte in benutzbarem (kopierbarem) Format posten? Bilder sind nicht sehr hilfreich.
Eher so?
Konto | Belegdatum | BU-Schlüssel |
8735 | 01.02.2021 | |
8736 | 12.07.2021 | 3 |
8736 | 12.07.2021 | 3 |
8736 | 12.07.2021 | 3 |
8736 | 21.10.2021 | 3 |
Die zu durchsuchende Tabelle sieht wie folgt aus
#BWA-Zeile | Bezeichnung | Konto von | Konto bis | S/H |
1010 | ||||
1020 | Umsatzerlöse | 8000 | 8195 | H |
1020 | Umsatzerlöse | 8200 | 8519 | H |
1020 | Umsatzerlöse | 8700 | 8799 | H |
1020 | Umsatzerlöse | 8950 | 8959 | H |
1040 | Best.Verdg. FE/UE | 8960 | 8989 | H |
1045 | Akt.Eigenleistungen | 8990 | 8999 | H |
Use your existing BWA table and then add this one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dcmxCcAwDATAVYJqI15vFEWzGO+/RkSMIY3L48aQJ7pLE5iCStAKl8z2zV0wKmJPX5OOw3geJjKzQFPDb+YL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Konto = _t, Belegdatum = _t, #"BU-Schlüssel" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Konto", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(BWA,(k)=>k[Konto von]<=[Konto] and [Konto]<=k[Konto bis])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"#BWA-Zeile"}, {"#BWA-Zeile"})
in
#"Expanded Custom"
Sorry, ich kann dir nicht folgen - was hat die Json damit zu tun. Ich habe eine Tabelle, in der die ganzen Daten gespeichert sind und nicht nur die 4 Zeilen - das war nur ein Ausschnitt. Kannst du mir da helfen?
Das ist kein JSON, das ist Power Query M code.
Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.
Your "Konto von" and "Konto bis" value ranges seem to be overlapping? What would be the expected outcome for Kontonummer 8600 ?
I would fill down the column "'#BWA_Zeile", means the result should always the column "BWA_Zeile" - in this case "1017"
My point is that 8600 should not be present in both ranges.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.