Excel Import with many to many relationship

[kofoedandreas]

Hi, 

I am currently trying to import data from an Excel sheet. I have a huge dataset hosted in Azure (I don't have server credentials), so my only option is to export all entities to an Excel sheet. 

One entity export could look something like: 

ID

Id	Name	Position	Bodyparts
1	John Doe	Manager	Arms
		Legs
		Head
		Torso
2	Jane Doe	Assistant	Arms
		Legs
		Torso

*Where each body part is a type of the entity (so we have an entity called bodyparts as well).

Therefore, the standard import option clearly fails. How do I work around this?

Best,
Tommy

[v-ljerr-msft]

Hi @kofoedandreas,

I'm not sure I can understand your problem totally. Did you receive any error when importing the Excel file.

What's your expected result? Is it like below?

let
    Source = Excel.Workbook(File.Contents("C:\Users\user1\Desktop\TestImport.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Id", Int64.Type}, {"Name", type text}, {"Position", type text}, {"Bodyparts", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Id", "Name", "Bodyparts"})
in
    #"Filled Down"

If not, could you post the expected result against the sample data? 

Regards

[kofoedandreas]

Hi PBI,

I want to import a huge dataset from a data source in Azure, but I don't have the server credentials which is why, I am exporting it all to Excel. 

My problem happens in relation to relationships. Look at the below:

Id	Name	Position	Bodyparts
1	John Doe	Manager	Arms
		Legs
		Head
		Torso
2	Jane Doe	Assistant	Arms
		Legs
		Torso

*The bodypart column is a entity itself and Torso is e.g. a type hereof. 

Then, the standard excel import clearly fails. How do I work around this?

Best,
Andreas