Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Replace multiple columns value base on a configuration table

My situation as below:

I have a Job table that contains Region and Area data of jobs:

Orginal.jpg

 and a configuration table

Configuration.jpg

 

 

 

 

 

 

I want to transform data of the Job table base on steps from the configuration table. Like below:

After step 1: 

AfterStep1.jpg

 

 

 

 

 

 

After step 2:

AfterStep2.jpg

 

After step 3:

AfterStep3.jpg

 

After step 4:

AfterStep4.jpg

 

After step 5:

AfterStep5.jpg

 

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

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Is this problem sloved? 
If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?
If not, please feel free to let me know.
 
Best Regards
Maggie
mahoneypat
Microsoft Employee
Microsoft Employee

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

 

 

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Thanks for your suggestion. But, I got below error with the first query:

 

Annotation 2020-06-14 144025.png

 

 

 

 

 

 

Besides, your solution seems to replace Region first, then replace Area. I would need to replace them in the order of the configuration file.

Anonymous
Not applicable

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

Anonymous
Not applicable

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)]))

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors