cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sifar786 Member
Member

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Pivot & append parameter table names as columns to another table with partial match and extr

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

6 REPLIES 6
Super User
Super User

Re: Pivot & append parameter table names as columns to another table with partial match and extr

Hi @sifar786 ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sifar786 Member
Member

Re: Pivot & append parameter table names as columns to another table with partial match and extr

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.

Super User
Super User

Re: Pivot & append parameter table names as columns to another table with partial match and extr

Hi @sifar786 ,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Pivot & append parameter table names as columns to another table with partial match and extr

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

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Super User
Super User

Re: Pivot & append parameter table names as columns to another table with partial match and extr

@ImkeF,

Always delivering great M Coding.

Regards,
MFelix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




sifar786 Member
Member

Re: Pivot & append parameter table names as columns to another table with partial match and extr

Hi @ImkeF ,

 

This is a really cool solution! Smiley Happy

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,585)