Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsYes, 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
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |