NESTER SET OF REFERENCES IN A SINGLE CELL IN EXCEL - EXTRACT TO CREATE RELATIONSHIP WITH OTHER SOURC

[Anonymous]

HI ALL, I need some help with something, which I am sure is fairly simple. I have a row in an excel table which has multiple references in a single cell. Example.

Purchase Order (PON) 520737-3;520741-6;5208271A;5208271A;5208271A 52G9426;22G7892;52G5263;52G5937;52G7213;22G7009;52G2343;52G9780;52G1148;22G9264;52G7570;52G0399;52G9020;52G4222;22G3130;52G4603;52G0765;52G6892;52G7890;52G4945;52G8303;22G2552;52G6843;52G0100;52G9733;52G8661 71800715;71800865;71800870;71800326;71800714 The column header is Purchase Order (PON) and each reference separated by a semi-colon relates to an individual purchase order reference. I need to be able to identify each reference "52G...."; and use it to link to an open purchase order report. Connecting the 2 will allow me to be able to attach tracking information to the open purchase order report. My concern as well is that there may be multiples of the same purchase order number in both data sets making relationship mapping difficult. Perhaps I could link both data sets to a list of supplier addresses where there is a common Supplier_ID. This might give me the relationships needed to link the data. Any assistance available would be highly appreciated. Many thanks Fabian

[Anonymous]

Hi Everyone 

I wonder if there is something available in Power Bi as  query or functionality which can help with the following issue.

I have a master data table which has correct names of suppliers and a unique supplier ID.

In the other data set which is shipping information, i have a list of addresses which are typed out inconsistetly. example

Shipper Name

SHIPPER NAME AMERICA

SHIPPER NAME USA

SHIPPER NAME CORP AMERICA

In the above example there is actually only 1 Shipper entitiy in the USA. In excel there is amanual function whereby you can "REPLACE ALL", which allows you to transform data to a single correct entry.

Is there a query or functionality within POwer BI to do this sort of thing? I could of course correct in excel prior to loading the data into POwer BI. However, My aim to link to specific folder and load the data from folder. Therefore it would be better to manipulate data in Power BI. Automatically or by running query rather than replce. But I will take what ever I can get at this stage.

I realize that the actual solution to better control the manual data entry. But as this is performed by a 3rd party, it will never be perfect.

Thank you

[Anonymous]

Hi

If i understand your requirement correctly, you want to do data cleansing where America , Corp America, USA all these values are same and you would like to replace America, Corp America by USA. Do you have these mappings as a data/ excel something ? 

Thanks
Raj

[Anonymous]

Hi Raj,

I do have some excel files, but they are quite difficult to share.

How do you suggest I can do that? Will a picture help?

[Anonymous]

I will explain a little further with more details.

I am not sure if sending an email direct from Outlook with work but I will give it shot.

If I look as a very basic relationship map it will look something like this

Main issue:

PMATES Master Data is bad. Different supplier ID but same name & address. This can be overcome by introducing unique ID.

Data entry from Shiping Data is manual and data control not implemented meaning multiples of supplier names eg Komatsu Germany GMBH where there are multiple entries of supplier name.

Key issue is that data in shipping data doesn’t have the same information as the master data file.

[Anonymous]

Hi Raj,

I do have some excel files, but they are quite difficult to share.

How do you suggest I can do that? Will a picture help?