Forum Discussion
Re-Using Combine and Transform Data query
Good morning,
I am new to Power BI and am trying to create an application and expand my knowledge.
I have two (almost identical) Excel workbooks... each containing the league data for previous seasons of a poker league that I am participating in.
The structure of the workbook is as follows:
| Worksheet | Data |
| Schedule | Contains two columns.
Example: 1 05/06/2022 |
1....13 (each sheet is named after the week of the league) | Contains two columns
Player: The 17 registered players in the league Rank: The position they finished in that week's poker game. Or 'Did Not Play' if they were not present |
I found that I was able to combine the matching sheet for both workbooks by:
- New Source - Folder
- Entering the directory containing my workbooks
- Combine & Transform Data
- Selecting the sheet
- Clicking OK
This results in both sheets from each workbook being loaded appended upon one another weith the Name of the workbook (which is GREAT because that is the Season Name).
Instead of repeating these steps for every sheet, I was wondering whether I could modify the Helper Queries to allow me to add an additional parameter to specify the name of the sheet, and then I can simply invoke the function by entering each sheet name?
I have tried to read through the query to identify where these modifications would need to take place, but have had no success. Can anyone please help me?
Kind regards,
Paul
Yes, though it's tricky to explain!
The helper queries are only so much "help" to you here, so you need to undo a bit of what Power Query did automatically.
So find "Transform Sample File" you have to delete the last few steps so that all you have left there is "Source".Now you should see a table where the first column ("Name") is the name of each sheet and the second column "Data" has an icon in the title bar on the right with two arrows pointing in opposite directions. Click on that, and then on "OK".
Now when you go back to your main query (perhaps called "Query1") it should all be sorted. Or you may have to delete the "Changed Type" column at the end. The sheet names are still in that first column.
Hope that helps, let me know if not.
Matt
============
If I helped please mark as solved and click on the thumbs up as this helps others as well as me!
3 Replies
- Greg_Deckler
Community Champion
Anonymous Can you post your M code?
- Coriel-11
Resolver II
Yes, though it's tricky to explain!
The helper queries are only so much "help" to you here, so you need to undo a bit of what Power Query did automatically.
So find "Transform Sample File" you have to delete the last few steps so that all you have left there is "Source".Now you should see a table where the first column ("Name") is the name of each sheet and the second column "Data" has an icon in the title bar on the right with two arrows pointing in opposite directions. Click on that, and then on "OK".
Now when you go back to your main query (perhaps called "Query1") it should all be sorted. Or you may have to delete the "Changed Type" column at the end. The sheet names are still in that first column.
Hope that helps, let me know if not.
Matt
============
If I helped please mark as solved and click on the thumbs up as this helps others as well as me!
Hi Coriel-11 ,
Apologies for the delay in responding. Your idea was great and has solved my problem. Thank you so much for your help
Kind regards,
Paul
