Forum Discussion
Tring to create a table based on row data from a query
Hi
I am new to Power Query, and am trying to create a table to display my query in a custom format (Sample below):
this is my Source - I want to move the values from the last 24 columns ("1Bottom" through "6Top") and make a new table from each row of this sample table.
| Transit | Address | City | Master Key | Ctrl Key | Route | Route Master Key | Key Code | Key Location Type | #of Keys | Site_Master.Site Number | MK1 | MK2 | MK3 | MK4 | MK5 | MK6 | CTRL1 | CTRL2 | CTRL3 | CTRL4 | CTRL5 | CTRL6 | USER1 | USER2 | USER3 | USER4 | USER5 | USER6 | 1Bottom | 1Master | 1Ctrl | 1Top | 2Bottom | 2Master | 2Ctrl | 2Top | 3Bottom | 3Master | 3Ctrl | 3Top | 4Bottom | 4Master | 4Ctrl | 4Top | 5Bottom | 5Master | 5Ctrl | 5Top | 6Bottom | 6Master | 6Ctrl | 6Top |
| 6612 | 270 ST. ANNE S ROAD SOUTHWEST | WINNIPEG | 806992 | 428570 | Y02-B | 826312 | 622314 | Old Branch | 15 | 8 | 0 | 6 | 9 | 9 | 2 | 4 | 2 | 8 | 5 | 7 | 0 | 6 | 2 | 2 | 3 | 1 | 4 | 6 | 2 | 6 | 9 | 0 | 2 | 10 | 11 | 2 | 4 | 12 | 5 | 3 | 6 | 6 | 8 | 1 | 8 | 8 | 6 | 2 | 2 | 6 | 13 | |
| 6612 | 270 ST. ANNE S ROAD SOUTHWEST | WINNIPEG | 806992 | 428570 | Y02-B | 826312 | 622310 | Branch | 15 | 8 | 0 | 6 | 9 | 9 | 2 | 4 | 2 | 8 | 5 | 7 | 0 | 6 | 2 | 2 | 3 | 1 | 0 | 6 | 2 | 6 | 9 | 0 | 2 | 10 | 11 | 2 | 4 | 12 | 5 | 3 | 6 | 6 | 8 | 1 | 8 | 8 | 6 | 0 | 2 | 8 | 13 |
and I want to get this result on the excel sheet - there are 6 Columns with 4 row values for each table
| 6612 | Master - 806992 | ||||
| 270 St Annes Road, SW | CTRL - 428570 | ||||
| Winnipeg | Old Branch | ||||
| Route Y02-B | 622314 | ||||
| 9 | 11 | 5 | 8 | 6 | 13 |
| 6 | 10 | 12 | 6 | 8 | 6 |
| 2 | 2 | 4 | 6 | 8 | 2 |
| 6 | 0 | 2 | 3 | 1 | 2 |
| 6612 | Master - 806992 | ||||
| 270 St Annes Road, SW | CTRL - 428570 | ||||
| Winnipeg | Branch | ||||
| Route Y02-B | 622310 | ||||
| 9 | 11 | 5 | 8 | 6 | 13 |
| 6 | 10 | 12 | 6 | 8 | 8 |
| 2 | 2 | 4 | 6 | 8 | 2 |
| 6 | 0 | 2 | 3 | 1 | 0 |
Hi Greg
You have not described your problem very well, which is probably why noone else has responded.
Just describe your point of difficulty rather excpect helpers to build the whole projects for you. 😀😀😀
This is not a criticism. Just advice. You will get quicker and better answers if you keep things simple. 😍😍😍
I am not sure what you are attempting but try this ....
In Power Query use the mouse to select all columns right of "Site_Master.Site Number"
for example MK1, MK2, MK3 to 6TOPthen click Transform ON the top menu and Unpiviot Columns > Unpiviot Columns
click on the Attribute column > Add columns > Extract > Fisrt Characters = 1
click on the Attribute column > Add columns > Extract > Range = 1 to 10
Click on the "First Characters" > Text Filters = 1,2,3,4,5,6Close and apply the changes
Add a matrix visual to a blank canvas and drag the columns as follows
I trust you have basic Power BI skills to builld the rest of the report, now you know how to use UNPIVOT.
Leran more about unpivot here
Please click the thumbs up and [accept solution] button
12 Replies
Hi Greg
You have not described your problem very well, which is probably why noone else has responded.
Just describe your point of difficulty rather excpect helpers to build the whole projects for you. 😀😀😀
This is not a criticism. Just advice. You will get quicker and better answers if you keep things simple. 😍😍😍
I am not sure what you are attempting but try this ....
In Power Query use the mouse to select all columns right of "Site_Master.Site Number"
for example MK1, MK2, MK3 to 6TOPthen click Transform ON the top menu and Unpiviot Columns > Unpiviot Columns
click on the Attribute column > Add columns > Extract > Fisrt Characters = 1
click on the Attribute column > Add columns > Extract > Range = 1 to 10
Click on the "First Characters" > Text Filters = 1,2,3,4,5,6Close and apply the changes
Add a matrix visual to a blank canvas and drag the columns as follows
I trust you have basic Power BI skills to builld the rest of the report, now you know how to use UNPIVOT.
Leran more about unpivot here
Please click the thumbs up and [accept solution] button
This l;ooks like what I want to accomplish - Thank you.
One more question: Where and how do I Add a matrix visual to a blank canvas and drag the columns as follows?
I looked through every choice in the menu bar, and found nothing close...
Hi Greg6630 ,
Thanks for reaching out to the Microsoft fabric community forum.Could you please share a screenshot showing where you got stuck while trying to add the Matrix visual? That will help me guide you more accurately.
Looking forward to your update so we can get this working just the way you need!
Best Regards,
Menaka.
Community Support Team
This is sort of using Power Query back to front!
Anyway, in the attached, there's your source table at cell A3 (Table8).
Below that at cell B8, there's a Power Query result table; change the Table8 data, then refresh the result table by right-clicking somewhere in it and choosing Refresh.
There's also another, connection only, query (Table8 (2)) which uses fewer steps (3) to achieve the same thing.
Hi Greg6630
Thanks for reaching out to the Microsoft fabric community forum.I would also take a moment to thank p45cal and speedramps , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Best Regards,
Menaka.
Community Support Team.Hi Greg6630 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you
Hi Greg6630 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution so that other community members can find it easily.
Thank you.
Hi again Greg
You are not being difficult.
However, please try do some Power BI training before asking question.
A matrix visual is very basic, which indicates you have not attempted any training.
I have provided some helpful free links. You will benefit from doing them.
I have previously provided an answer with an example. Please can you click the thumbs up and [accept solution] buttons. Thank you.
https://learn.microsoft.com/en-us/training/modules/get-started-with-power-bi/
Matrix help ....
https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-matrix-visual
Hi again Greg6630
I think we have given you the answer ( a few times in fact ) 😀
Please can you be nice and click [accept solution]
or let use know what is outstanding.Thank you 😀
