Forum Discussion
Pivot & append parameter table names as columns to another table with partial match and extraction
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?
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
6 Replies
- MFelix
Super User
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
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
