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'm new to Power BI and the M (or power query) language, so any help is really appreciated!
I have the following data:
Case | Description |
1 | from City1 to City2 |
1 | from City2 to City1 |
2 | from City1 to City3 |
2 | between City1 and City3 |
2 | from City3 to City1 |
What I would like to have is this:
Case | Description | From | To |
1 | from City1 to City2 | City1 | City2 |
1 | from City2 to City1 | City2 | City1 |
2 | from City1 to City3 | City1 | City3 |
2 | between City1 and City3 | City1 | City3 |
2 | from City3 to City1 | City3 | City1 |
I have a VBA code that does this, but I'd like to see if it's possible to do it directly in Power BI. The code basically does this:
1. separate the Description elements by blanks and put them into an array (list)
2. search the array for the keywords "to" or "and"
3. set N = index of "to" or "and"
4. set From column = array[N-1] and To column = array[N+1]
Any ideas how I could do the same in Power BI with the advanced query editor?
Solved! Go to Solution.
This would be one possibility:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY69DoJAEIRfZWNtCD8aEztjNBZqo51SLNxiQLgjeweGN/FR6Hkx8ZTCn2p38s1M5nQaeZCwKmCZmsYDo+zjwygcfyB/QN4L+X9SwYAiMjci+aYoxRv/RIOv1iNjkqRxH5GqiqkgaaAqBRoSc1hNpnDoWimIs0ZfCXbKKK4pG78K19y1Oqf62bnB3mXFsOVcuW4Qz1x7CRYYEUtGhL0d2N21IYZt116qwioHVqgNsEIBqYYStcYoJ6dfGj4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Description " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Description ", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Case Description ",Splitter.SplitTextByAnyDelimiter({" from ", " between", " to ", " and "}, QuoteStyle.Csv),{"Case Description .1", "Case Description .2", "Case Description .3"}), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Case Description .2", Text.Trim}, {"Case Description .3", Text.Trim}}), #"Added Custom" = Table.AddColumn(#"Trimmed Text", "From", each List.First(Text.Split([Case Description .2], " "))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "To", each List.First(Text.Split([Case Description .3], " "))), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"From", "To"}) in #"Removed Other Columns"
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
This is pretty easy using the UI
Load the data to the editor
on the description column, split the column based on delimiter, space, every instance
Delete the columns that you don't need
Rename the columns you keep.
Hi Matt,
thanks for your reply. Actually my data looks much messier than this - sometimes there are more elements on the left, sometimes more on the right, so splitting the columns and deleting unwanted ones is not an option, unless I can specifically (through a script) identify the column between "from" and "to".
Do you have any suggestions how I could do that with a function?
You can split your column on your key-terms like this:
= Table.SplitColumn(#"Changed Type","Case Description ",Splitter.SplitTextByAnyDelimiter({" to ", " and "}, QuoteStyle.Csv),{"Case Description .1", "Case Description .2"})
The "Splitter.SplitTextByAnyDelimiter"-function takes a list-argument where you can put in as many splitting-values as you need.
For the further cleanup it would be necessary to know more details of your data.
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
I tried using the Splitter function, though I'm not sure I understand how I can get exactlly the element I want.
This is one string I'm trying to scan:
Traffic annoucement updated: E45 Sønderjyske Motorvej, from Frøslev to Haderslev between <70> Aabernraa N and Øster Løgum Øst. East road is passable.
Now I would ideally like to have Frøslev in a "From" column and Haderslev in a "To" column. It's already enough if I only get the from column.
So far I've written this:
if ( (Text.Contains([Activity description], "from") and (Text.Contains([Activity description], "to")) ))
then (List.Last(Text.Split(List.First(Text.Split([Activity description], "to")),"from")))
else null
This works most of the times, but when there is for example a second "from" or "to" in the string, the function doesn't get it right. If I could set a condition to only take the string between "from" and "to" it would be almost perfect.
This would be one possibility:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY69DoJAEIRfZWNtCD8aEztjNBZqo51SLNxiQLgjeweGN/FR6Hkx8ZTCn2p38s1M5nQaeZCwKmCZmsYDo+zjwygcfyB/QN4L+X9SwYAiMjci+aYoxRv/RIOv1iNjkqRxH5GqiqkgaaAqBRoSc1hNpnDoWimIs0ZfCXbKKK4pG78K19y1Oqf62bnB3mXFsOVcuW4Qz1x7CRYYEUtGhL0d2N21IYZt116qwioHVqgNsEIBqYYStcYoJ6dfGj4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Description " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Description ", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Case Description ",Splitter.SplitTextByAnyDelimiter({" from ", " between", " to ", " and "}, QuoteStyle.Csv),{"Case Description .1", "Case Description .2", "Case Description .3"}), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Case Description .2", Text.Trim}, {"Case Description .3", Text.Trim}}), #"Added Custom" = Table.AddColumn(#"Trimmed Text", "From", each List.First(Text.Split([Case Description .2], " "))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "To", each List.First(Text.Split([Case Description .3], " "))), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"From", "To"}) in #"Removed Other Columns"
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
Thank you!
Covering 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 |
---|---|
116 | |
100 | |
69 | |
68 | |
44 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |