Forum Discussion
Help needed to get this done.
My desired input table and output table. I already used the transposing method and split the query then appended that but I need to automate this without any manual interactions.
The output should be valid even if I add more data (let us say RM = C, D, E etc...) under the input table.
Thanks in advance....
Hi Anonymous
For this I would consider creating a Power Query function, you can see a Wise Owl video here.In brief we want to:
- Set up a query for a single RM making it look how we want
- Parameritise as a filter the RM
- Create a function which can pass values to this parameters
- Loop over a list of RM's appending everything together
First create the parameter and set it to a single RM:
Next filter the list you have to a single RM, the one in the parameter:
Remove RM and Transpose the columns
Promote the headers and then right click on the Query and choose create function:
Connect to the data source again but this time remove other columns and duplicate:
On the Add Columns tab choose Invoke Custom Function:
Enjoy your end result:
If this helped please do mark as an answer for other 🙂
8 Replies
- SamWiseOwl
Super User
Hi Anonymous
For this I would consider creating a Power Query function, you can see a Wise Owl video here.In brief we want to:
- Set up a query for a single RM making it look how we want
- Parameritise as a filter the RM
- Create a function which can pass values to this parameters
- Loop over a list of RM's appending everything together
First create the parameter and set it to a single RM:
Next filter the list you have to a single RM, the one in the parameter:
Remove RM and Transpose the columns
Promote the headers and then right click on the Query and choose create function:
Connect to the data source again but this time remove other columns and duplicate:
On the Add Columns tab choose Invoke Custom Function:
Enjoy your end result:
If this helped please do mark as an answer for other 🙂
I am so greatful for your effort and Could you please help me to solve this one also
Input TableRM Column 1 Column 2 Column3 A TO 1 2 A OH 2 2 A Date 01-01-2024 01-02-2024 A Status Actual Planned A TO 22 12 A OH 12 33 A Date 01-03-2024 01-04-2024 A Status Planned Planned B TO 8 10 B OH 12 14 B Date 01-01-2024 01-02-2025 B Status Actual Actual B TO 12 23 B OH 32 13 B Date 01-03-2024 01-04-2024 B Status Planned Planned my output result should be
Output RM To OH Date Status A 1 2 01-01-2024 Actual A 2 2 01-02-2024 Planned A 22 12 01-03-2024 Planned A 12 33 01-04-2024 Planned B 8 12 01-01-2024 Actual B 10 14 01-02-2025 Actual B 12 32 01-03-2024 Planned B 23 13 01-04-2024 Planned - SamWiseOwl
Hi Anonymous ,
What is the difference between this and the original data? If the only difference is additional lines the function can handle additional lines and columns 🙂
If you would like the original PBIX file so you can modify click here.Paste the modified data into the source step of both queries or even connect to an Excel file instead.
Hope this helps!
- mickey64
Step 0: I use these data below. (Date:yyyy/mm/dd)
Step 1: I unpivot 2 columns on Power Query Editor.
- Before -
- After -
Step 2: I make a matrix on Power BI Desktop.
- SamWiseOwl
That is so sensible, great job!
