Forum Discussion
Solved
Display data on a single row
I am fairly new to Power BI and I need help getting all relational data on a single line.
I am using direct query to import data from a number of sql tables. My data is coming in have multiple lines per single transaction as seen below...
Query 1
| Identifier | DispensationKey | SessionIdentifier | PostNumber | Device | Cancelled | Completed | Validated |
| 2711641 | 6710aba5-54d2-462a-bfe9-73b774fa3298 | 5E+06 | 1 | Cash Dispenser | 0 | 1 | 0 |
| 2711642 | ea7fd19d-fc5f-40ad-863f-4452c25bc410 | 5E+06 | 1 | Coin Dispenser | 0 | 1 | 0 |
Query 2
| Identifier | DispensationIdentifier | Source | Type | Value | QuantityDispensed | QuantityRejected | QuantityRequested | NewQuantity | NewValue | QuantityNotTaken | CurrencyIdentifier |
| 4545517 | 2711641 | 1 | Cash | 100 | 4 | 0 | 4 | 1482 | 148200 | 0 | XXX |
| 4545518 | 2711641 | 3 | Cash | 5 | 2 | 0 | 2 | 803 | 4015 | 0 | XXX |
| 4545519 | 2711641 | 4 | Cash | 1 | 1 | 0 | 1 | 1124 | 1124 | 0 | XXX |
| 4545520 | 2711642 | 4 | Coin | 0.01 | 2 | 0 | 2 | 169 | 1.69 | 0 | XXX |
| 4545521 | 2711642 | 2 | Coin | 0.05 | 4 | 0 | 4 | 18 | 0.9 | 0 | XXX |
| 4545522 | 2711642 | 3 | Coin | 0.05 | 0 | 0 | 0 | 88 | 4.4 | 0 | XXX |
| 4545523 | 2711642 | 1 | Coin | 0.25 | 1 | 0 | 1 | 25 | 6.25 | 0 | XXX |
In order to process this data effectively in Power BI I need all data to be displayed on a signle line like so...
| Identifier | DispensationKey | SessionIdentifier | PostNumber | Device | Cancelled | Completed | Validated | Identifier | DispensationKey | SessionIdentifier | PostNumber | Device | Cancelled | Completed | Validated | Identifier | DispensationIdentifier | Source | Type | Value | QuantityDispensed | QuantityRejected | QuantityRequested | NewQuantity | NewValue | QuantityNotTaken | CurrencyIdentifier | Identifier | DispensationIdentifier | Source | Type | Value | QuantityDispensed | QuantityRejected | QuantityRequested | NewQuantity | NewValue | QuantityNotTaken | CurrencyIdentifier |
| 2711641 | 6710aba5-54d2-462a-bfe9-73b774fa3298 | 5360553 | 1 | Cash Dispenser | 0 | 1 | 0 | 2711642 | ea7fd19d-fc5f-40ad-863f-4452c25bc410 | 5360553 | 1 | Coin Dispenser | 0 | 1 | 0 | 4545517 | 2711641 | 1 | Cash | 100 | 4 | 0 | 4 | 1482 | 148200 | 0 | XXX | 4545518 | 2711641 | 3 | Cash | 5 | 2 | 0 | 2 | 803 | 4015 | 0 | XXX |
INDIA34 , The Query1, and Query 2 formats are correct and good for power bi
In a Matrix visual you can have source on column and repeat whatever column you want .
Or unpivot using source
1 Reply
INDIA34 , The Query1, and Query 2 formats are correct and good for power bi
In a Matrix visual you can have source on column and repeat whatever column you want .
Or unpivot using source