cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RafaelKnuth Regular Visitor
Regular Visitor

Challenge: Transforming Data with M-Language - messy data scraped from the web

I am new to M-Language, and I am playing around with it to see how far I can get.

 

I pulled data from the web: https://www.t-systems.com/de/en/locations for the purpose of creating a clean directory of company locations.

 

After several failed attempts to get data directly from the web into Excel / Power BI, I tried several web scrapers, of which only one worked halfways well.

 

Finally, I got an Excel file that looks like this:

I am trying to wrap my head around this data, and I was wondering if it's even possible to clean that data up with help of M?

 

It's barely digestable for a human ...

 

Obviously, it would take me less time to just manually copy and paste the data into a text file, clean it up manually and then load it into Excel. But I was wondering how a data pro would handle such a scenario, given the data set wouldn't be just a couple dozen addresses but a huge volume, impossible to get cleaned up manually.

 

Please bear in mind that the example above is totally arbitrary, based on publically availeable data, for educational purposes only.

I am using it to demostrate a realistic scenario.

 

Thanks for your feedback!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Challenge: Transforming Data with M-Language - messy data scraped from the web

HI @RafaelKnuth,

 

Current power query not contain functions to auto analysis records.

 

According to your screenshots, I think you can use try to get data from specific api, then remove 't-system' prefix from address column.

 

After these steps, I think remain part text can be analysed as 'data category' address/place. You can use these records to create map.

 

Reference link:

Using a REST API as a data source

5 Very Useful Text Formulas – Power Query Edition

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
RafaelKnuth Regular Visitor
Regular Visitor

Re: Challenge: Transforming Data with M-Language - messy data scraped from the web

Thank you so much @v-shex-msft are you aware of any tools that perform auto analysis records?

That data set is unfortunately not available through REST APIs, I had to scrape it from the site which is I guess why it's so messy.

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,764)