Pivot & append parameter table names as columns to another table with partial match and extraction

[Anonymous]

I have a single column ParameterTable:

Parameters
============
country
categoryName
manufacturers
charNames

I want to Pivot and Append this table to another table named URLTable :

SomeColName	URL
======================
some value	/
other value	/main
values	        /main/country=USA
no of values	/main/country=FRA&categoryName=DigitalSign
more values	/main/country=ESP&categoryName=MornDew&manufacturers=RoyalTigers
multiple values	/main/country=ESP&categoryName=Plastics&manufacturers=Microtech&charNames=HasFlavour
single value	/main/country=UK&categoryName=Wood&manufacturers=Xenobia&charNames=WithMintTaste
another value	/main/country=ESP&categoryName=Paper&manufacturers=SimplexTech&charNames=WithVarnish

Inorder to get this FinalTable with the Parameter values extracted automatically from the URL column and placed into the respective Parameter columns :

SomeColName	URL	                                                                                country	categoryName	manufacturers	charNames
==========================================================================================================================================================
some value	/				
other value	/main				
values	        /main/country=USA	                                                                USA			
no of values	/main/country=FRA&categoryName=DigitalSign	                                        FRA	DigitalSign		
more values	/main/country=ESP&categoryName=MornDew&manufacturers=RoyalTigers	                ESP	MornDew	        RoyalTigers	
multiple values	/main/country=ESP&categoryName=Plastics&manufacturers=Microtech&charNames=HasFlavour	ESP	Plastics	Microtech	HasFlavour
single value	/main/country=UK&categoryName=Wood&manufacturers=Xenobia&charNames=WithMintTaste	UK	Wood	        Xenobia	        WithMintTaste
another value	/main/country=ESP&categoryName=Paper&manufacturers=SimplexTech&charNames=WithVarnish	ESP	Paper	        SimplexTech	WithVarnish

 Here is the Sample example. Is there a quick and efficient way to do this in Excel 2016 Power Query?

[MFelix]

Hi @Anonymous ,

To what I can understand you want to retrieve the Parameter values from your URLTable correct?

Looking at your data believe that the best option is to pick up the URLTable and add columns that get those values no need to use the parameter table, In your case you should use Text between delimiters formula. But using the columns by examples you can get it to work correctly.

I also made some changes to the last column since the last value will pick the last character if the charNames doesn't exist in the lines.

See code below and file attach.

let
    Source = Excel.CurrentWorkbook(){[Name="URLTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SomeColName", type text}, {"URL", type text}}),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Country", each Text.BetweenDelimiters([URL], "=", "&"), type text),
    #"Inserted Text Between Delimiters1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Category", each Text.BetweenDelimiters([URL], "=", "&", 1, 0), type text),
    #"Inserted Text Between Delimiters2" = Table.AddColumn(#"Inserted Text Between Delimiters1", "Manufacturers", each Text.BetweenDelimiters([URL], "=", "&", 2, 0), type text),
    #"Added Custom Column" = Table.AddColumn(#"Inserted Text Between Delimiters2", "CharNames", each if Text.Length (Text.Combine({Text.Reverse(Text.Middle(Text.Reverse(Splitter.SplitTextByDelimiter("=", QuoteStyle.None)([URL]){4}?), 1)), Text.Middle([SomeColName], 13, 1), Text.End([URL], 1)})) = 1 then "" else (Text.Combine({Text.Reverse(Text.Middle(Text.Reverse(Splitter.SplitTextByDelimiter("=", QuoteStyle.None)([URL]){4}?), 1)), Text.Middle([SomeColName], 13, 1), Text.End([URL], 1)}))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column",{{"CharNames", type text}})
in
    #"Changed Type1"

Regards,

MFelix

[Anonymous]

Thanks for your reply @MFelix .

The reason why i have the Parameters table is because the URLs may change and so will the parameters be different everytime. So having a Parameters table, will help me update the parameters everytime. Then i can just Pivot the Parameters table horizontally somehow and Append it to the URL table. Appending should automatically add the Parameter names as new Columns to the URL table quickly. So i can save steps adding columns everytime, as the parameter column names may change everytime.

The difficult part is to add formulae to each column to extract the parameter values from the URL column. Hope you are getting my point.

[MFelix]

Hi @Anonymous ,

I have tried a solution but believe it's to ocmplicated.

I'm reaching out to @ImkeF a great M language expert, for sure she can help.

@ImkeF  do you think is possible to make a loop that goest trough each parameter to create the data required or something similar?

My solution pass trough having a specific number of parameters and then getting values from the URL between parameter 1 and parameter 2 and so on this will give a lot of null if you only have 2 parameters for example.

Regards,

MFelix

[ImkeF · Accepted solution]

Yes, I have a very cool dynamic method for it:

let
    Source = URLTable,
    NewColumns = List.Transform(ParameterTable[Parameters], 
                                each List.Transform(Source[URL], 
                                                    (inner)=> Text.BetweenDelimiters(inner, _ & "=", "&"))),
    Result = Table.FromColumns(Table.ToColumns(Source) & NewColumns, Table.ColumnNames(Source) & ParameterTable[Parameters])
in
    Result

[Anonymous]

Hi @ImkeF ,

This is a really cool solution! 

I would like to understand how it works. Maybe a brief para or small video tutorial explaining it's workings would really help.

One Question: 

How do i grab the '&' and its succeeding text in URL parameter values? 

e.g.

/Main/index.html?country = USA&categoryName = Alcoholic & Non-AlcoholicDrinks

where '&' is a part of CategoryName. Right now, the & Non-AlcoholicDrinks part gets truncated off and only the Alcoholic part remains in the CategoryName column.

@MFelix Thanks for your help and guidance.

[MFelix]

@ImkeF,

Always delivering great M Coding.

Regards,
MFelix