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 list of Values coming from a source SQL. This list of values is related to a Sales Order and is not in the a "clean" format for configuration buidling. My idea is to have a table as follows:
Where the data represents a single designed configuration of values based on the Sales Order. Example in row 1,
for "-S01" the values are "65K AIC SYM", "50 DEGREES C", etc.
I then want to convert this using values that are "clean" in my opinion. For this, I have a value to value conversion table. For example, the value "65K AIC SYM" converts to "65K".
My final table I would like to look like this:
Col 1 Row 1 = hmb-->65K AIC SYM//50 DEGREES C//...
Col 2 Row 1 = hmb-->65K//50C//...
This will allow Col 1 to relate to my customer sales orders and Col 2 to relate to my configuration. Any help would be greatly appreciated as I have been stuck on this for some time.
hello, @zbaker83 first you need to wrap your replacements into a record with "old" values as record fields and "new" values as field values like [65K AIC SYM = 65K, 50 DEGREES C = 50C, ...]. You can create this manually but better to create this table in Excel. This should be table with 2 columns: Name and Value. Then load it to PQ and apply Record.FromTable function to this table.
rec = Record.FromTable(rec_tbl)
this gives you your record. Then use it in Record.FieldOrDefault function like this:
new_col = Table.AddColumn(
data_table, "my_configuration",
(x) =>
[lst = Splitter.SplitTextByAnyDelimiter({"//", "/"})(x[data]),
tx = List.Transform(lst, (w) => Record.FieldOrDefault(rec, w, w)),
txt = Text.Combine(tx, "//")][txt]
)
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.