Hello together,
I´m struggling with following challenge:
I have already a table in Power Query loaded, where I have three columns with multiple results. (Start situation; column A-D)
- First column is always filled with an name; there might be multiple in.
- Second and third column have always these "; " according the number of names from first column
Is there a way, to match thee values according as my example on column K-N?
(I´m sorry for my worse English, therefore I´ve created that example.)
You could use something like this:
let
Source = Excel.CurrentWorkbook(){[Name="source_table"]}[Content],
#"Replaced Value1" = Table.ReplaceValue(Source,"; ",";",Replacer.ReplaceText,{"Names", "Countries", "Cities"}),
AddedCustom = Table.AddColumn(#"Replaced Value1", "C", each Table.FromColumns({
if [Names] = null then {null} else Text.Split(Text.Trim([Names]),";"),
if [Countries] = null then {null} else Text.Split(Text.Trim([Countries]),";"),
if [Cities] = null then {null} else Text.Split(Text.Trim([Cities]),";")},{"Names2","Countries2", "Cities2"})),
#"Removed Columns" = Table.RemoveColumns(AddedCustom,{"Names","Countries", "Cities"}),
#"Expanded C" = Table.ExpandTableColumn(#"Removed Columns", "C", {"Names2","Countries2", "Cities2"}, {"Names","Countries", "Cities"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded C", each [Names] <> null and [Names] <> ""),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","",";",Replacer.ReplaceValue,{"Countries", "Cities"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value",null,";",Replacer.ReplaceValue,{"Countries", "Cities"})
in
#"Replaced Value2"
Hello Velouria,
wow, I´m impressed, it works great. 🙂
I didn´t fully understand that adding and expanding of column "C", but I will look into it further to get that understanding
