Forum Discussion
One to Many Record Dilemma
Greetings Gang,
I have a situation where a client of mine is pulling in data into SharePoint from an InfoPath file that uses repeating tables - So when I connect the list records from the form to an Excel file, I get one record, with multiple entries from the repeating table in one cell.
Is there a way to convert the one row with the multiple entries in the cell to seperate it out as multiple rows. My screenshot should give you a better representation of what I am looking for. Any insight would be appreciated. Thank you!
Hi Matt, you could connect to your source files with Power Query and then use the 'Fill Down' functionality to fill in empty spaces below with the values of the non-empty rows above them.
- Unfortunately, I don't think this is going to work as expected, another method I tried is where I have all my data repeating in a single row of cells (3 entries in one cell for each child item) - then I am trying to figure out a way to "parse" that information out into three separate cells. Let me know if you have any suggestions or ideas. Thank you!
MattHi Matt,
IMHO, what Yury suggested is the most straightforward and reliable way to do the transformation. Better to transform data on query level, and Get&Transform is the perfect tool to do that.
If you export your list to Excel from Sharepoint and do any transformations within the resulting table they will be lost with each refresh.
Did you try G&T and what exactly doesn't work?
- Thank you, Yury, I'll give that a try and see how it works! I'll keep the board posted on the results!
Matt
