Log In
Register
Home
August 21, 2019
Offline
Hi,
| Sought from | Received from | Final Output |
| AA BB CC | BB | AA CC |
| AA BB CC | AA CC | BB |
| AA BB CC | CC | AA BB |
| AA BB CC DD | DD AA CC | BB |
each time the number of sub-string in "received from" increases, for e.g DD AA BB CC (4 nos.), I need to amend the code in Power Query
any other more efficient way in Power Query to produce final output ?
Thank you !
Thank you
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Offline
Try this:
= Table.AddColumn(#"Removed Other Columns", "Custom", each Text.Combine(List.RemoveMatchingItems(Text.Split([Sought from]," "),Text.Split([Received from]," "))," "))
Answers Post
August 21, 2019
Offline
Hi Catalin Sir,
thanks for your M code, however when I inserted in the applied steps it gives me error
perhaps you can explain to me the entire codes (insert it in the applied steps) for my learning
Need to consult you on my test query(attached), trying to use another method to achieve what I want, the last records should have 4 columns and not sure why it transposed to 3 columns only?
thank you
August 21, 2019
Offline
Hi Catalin,
thanks for your List.RemoveMatchingItems code, I think manage to got the solution I want, the rest will be just some trimming and combining
Really appreciate you vast knowledge on M Coding
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sought from", type text}, {"Received from", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Master", each Text.ToList([Sought from])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "SubString", each Text.ToList([Received from])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Remove_Match", each List.RemoveMatchingItems([Master],[SubString])),
#"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Remove_Match", each Text.Combine(List.Transform(_, Text.From)), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Master", "SubString"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Remove_Match", "Final Results"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Final Results", Text.Trim, type text}})
in
#"Trimmed Text"
1 Guest(s)
