Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
My situation as below:
I have a Job table that contains Region and Area data of jobs:
and a configuration table
I want to transform data of the Job table base on steps from the configuration table. Like below:
After step 1:
After step 2:
After step 3:
After step 4:
After step 5:
I would like to dynamically load the configuration file from a CSV file so that I can add step 6,7,8, etc. at runtime.
I wonder if we can do that with Power Query.
Thanks in advanced
Hi @Anonymous
This looks like it can be solved with two bulk replaces. Please paste the 3 M queries into 3 blank queries to see how to do that with your example data. Two of the queries are the replace lists (of lists) from your Configuration table and the third is your main table that gets the values replaced.
Query 1
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNLcpMTlTSUQpPLS5RcM5PLC5RitVBlnBNRJFwTsxLTAGJB+eXlmSkFuUpAEWAAqiaHHMSi7PRBZ1T80qKEnMUHItSgVKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Area = _t]),
ReplaceWithList1 = Table.TransformColumns(Source, {{"Region", each Text.Combine(List.ReplaceMatchingItems({_}, ReplaceList1)), type text}}),
ReplaceWithList2 = Table.TransformColumns(Source, {{"Area", each Text.Combine(List.ReplaceMatchingItems({_}, ReplaceList2)), type text}})
in
ReplaceWithList2
Query 2
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMTS3KTE4EssJTi0sUnPMTi0swhR2LUhOVYnWilYxQ5FwTsWoBC8O1GKPIAZFfflFJhgKSEEiVCZDhnJiXmIJPkSkWCcecxOLsRCwSUD7YGbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Step No" = _t, #"From Region" = _t, #"From Area" = _t, #"To Region" = _t, #"To Area" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Step No", Int64.Type}, {"From Region", type text}, {"From Area", type text}, {"To Region", type text}, {"To Area", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"From Region", "To Region"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each {[From Region],[To Region]}),
#"Removed Duplicates" = Table.Distinct(#"Added Custom", {"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([To Region] = "North America") and ([From Region] <> "North America")),
Custom = #"Filtered Rows"[Custom]
in
Custom
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLMTS3KTE4EssJTi0sUnPMTi0swhR2LUhOVYnWilYxQ5FwTsWoBC8O1GKPIAZFfflFJhgKSEEiVCZDhnJiXmIJPkSkWCcecxOLsRCwSUD7YGbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Step No" = _t, #"From Region" = _t, #"From Area" = _t, #"To Region" = _t, #"To Area" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Step No", Int64.Type}, {"From Region", type text}, {"From Area", type text}, {"To Region", type text}, {"To Area", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"From Area", "To Area"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each {[From Area],[To Area]}),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([To Area] <> "")),
Custom = #"Filtered Rows1"[Custom]
in
Custom
Query 3
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks for your suggestion. But, I got below error with the first query:
Besides, your solution seems to replace Region first, then replace Area. I would need to replace them in the order of the configuration file.
try and test (I'm not sure I understand your needs and filtering rules) these:
let
nsteps=Table.RowCount(ruleTab),
steps=List.Accumulate({1..5},dataTab,
(s,c)=>Table.FromRecords(Table.TransformRows(s, each _&[region=toRegion(ruleTab{c-1},_),area=toArea(ruleTab{c-1},_)])))
in
steps
toRegion function
let
match=(stepN,dTabRow) =>
let
dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,{"region"})){0},
newValue= if stepN[from region]=dTabRowValues then stepN[to region] else dTabRow[region]
in
newValue
in
match
and similar toArea function:
let
match=(stepN,dTabRow) =>
let
dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,{"area"})){0},
newValue= if stepN[from area]=dTabRowValues then stepN[to area] else dTabRow[area]
in
newValue
in
match
this if you intend to have all the intermediate steps and not just the final result:
let
nsteps=Table.RowCount(ruleTab),
steps=List.Accumulate({1..5},{dataTab},
(s,c)=>s&{Table.FromRecords(Table.TransformRows(List.Last(s), each _&[region=toRegion(ruleTab{c-1},_),area=toArea(ruleTab{c-1},_)]))})
in
steps
let me know if this is what you were looking for
I'm not sure I understand what the need is. But perhaps the following function, originally designed for a similar problem, could be a good starting point, appropriately adapting the names of the columns
let
match=(rTab,dTabRow,rCols) =>
let
dTabRowValues= Record.FieldValues(Record.SelectFields(dTabRow,rCols)),
newCity= try Table.SelectRows(rTab, each List.ContainsAll( Record.FieldValues(_),dTabRowValues))[ActualCity]{0} otherwise dTabRow[City]
in
newCity
in
match
Here how to call the function:
for each row of data table search a matching roe in the configuration table (comparing only relevant column value) and replacing old value with new value
Table.FromRecords(Table.TransformRows(dataTab, each _&[City=matchCity(ruleTab,_,relCols)]))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
69 | |
24 | |
18 | |
13 |