Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
wooand
Helper II
Helper II

Aggregate alternate rows

‎11-23-2017 10:40 AM

OK, so this one has me totally stumped.

 

I have a list of swap transactions, where financial instruments are exchanged on day one, and are swapped back on day x at the end of the term. 

 

For each transaction I have a reference, a trade date, a buy or sell and a rate.  For each transaction I then have the opposite data on the next line.  This means I have a table with all the correct data.

 

Now I need to put the data in line 2 alongside the data in line 1 so that I have a single line per transaction.  I can then add the details I need to add into a new column and off we go.  Except I have no clue how to place the details in line 2 into columns next to the original line 1 trade.

 

Any clues???  

 

Labels:
Message 1 of 7
888 Views
0
1 ACCEPTED SOLUTION
MarkS
Resolver IV
Resolver IV

‎11-23-2017 11:47 AM

Hi @wooand,

 

To get the second line of data you could try this pattern.

1. Add a new index column starting at 1

2. Add another new index column starting at 0

3. Merge Query -  using the same table (left outer join)  merge on the index columns (first index column from the table shown on the top with the second index column from the table on the bottom.

4. Transform one of the index columns with modulo 2 (from the standard math transformations)

5. Filter out that column on 1

6. Expand the table

 

MarkS

 

View solution in original post

Message 4 of 7
996 Views
0
6 REPLIES 6
MarkS
Resolver IV
Resolver IV

‎11-23-2017 11:47 AM

Hi @wooand,

 

To get the second line of data you could try this pattern.

1. Add a new index column starting at 1

2. Add another new index column starting at 0

3. Merge Query -  using the same table (left outer join)  merge on the index columns (first index column from the table shown on the top with the second index column from the table on the bottom.

4. Transform one of the index columns with modulo 2 (from the standard math transformations)

5. Filter out that column on 1

6. Expand the table

 

MarkS

 

Message 4 of 7
997 Views
0
wooand
Helper II
Helper II
In response to MarkS

‎09-18-2018 06:24 AM

Mark,

 

I have a question.  I have been using your answer to this question for some time with great success, but I am struggling when I have two rows the same.  See below - lines 5 and 6 are the same amount - but when I use this process to bring the lines alongside one another the duiplicated information seems to create a problem in that it only mirrors the row once, thereby making every line one line out.  I've tried using 'Full Outer' but that didn't work.  

 

Any ideas?

 

A.

 

 

Capture.PNG

Message 6 of 7
761 Views
0
MarkS
Resolver IV
Resolver IV
In response to wooand

‎09-18-2018 04:32 PM

Hi @wooand,

I cannot re-create what you are experiencing.  When I try it this is what I am getting:

 Community 09-18-2018.PNG

Message 7 of 7
754 Views
0
wooand
Helper II
Helper II
In response to MarkS

‎11-23-2017 11:57 AM

Well done Mark, that's nailed it.

 

Many thanks.

 

 

Message 5 of 7
865 Views
0
Zubair_Muhammad
Community Champion
Community Champion

‎11-23-2017 11:31 AM

Hi @wooand

 

If each transaction has a unique reference and 2 rows only, I believe it should be doable

 

Could you paste some sample data and expected reults?

 

May be 10-15 rows of sample data and your desired RESULT


Regards
Zubair

Please try my custom visuals
Message 2 of 7
876 Views
0
wooand
Helper II
Helper II
In response to Zubair_Muhammad

‎11-23-2017 11:45 AM

Thanks Zubair.  I've taken the amounts out, but I'm sure you get the gist:

 

PairTrade DateValue DateTrade TypeCUST Side AllocationAmountBUY CCYSELL CCYUSD CostUSD Amount
EURCHF03/01/201706/01/2017SWAPSELL10.00CHFEUR1.0010.00
EURCHF03/01/201711/01/2017SWAPBUY10.00EURCHF1.0010.00
EURCHF03/01/201706/01/2017SWAPBUY10.00EURCHF1.0010.00
EURCHF03/01/201711/01/2017SWAPSELL10.00CHFEUR1.0010.00
EURGBP03/01/201701/02/2017SWAPBUY10.00EURGBP1.0010.00
EURGBP03/01/201704/01/2017SWAPSELL10.00GBPEUR1.0010.00
EURGBP03/01/201704/01/2017SWAPSELL10.00GBPEUR1.0010.00
EURGBP03/01/201701/02/2017SWAPBUY10.00EURGBP1.0010.00
EURGBP03/01/201731/01/2017SWAPSELL10.00GBPEUR1.0010.00
EURGBP03/01/201705/01/2017SWAPBUY10.00EURGBP1.0010.00
EURGBP03/01/201731/01/2017SWAPBUY10.00EURGBP1.0010.00
EURGBP03/01/201705/01/2017SWAPSELL10.00GBPEUR1.0010.00
EURGBP03/01/201711/01/2017SWAPBUY10.00EURGBP1.0010.00
EURGBP03/01/201706/01/2017SWAPSELL10.00GBPEUR1.0010.00

 

And the target is to have the far legs currently on the second row captured alongside their near legs:

 

PairTrade DateValue DateTrade TypeCUST Side AllocationAmountBUY CCYSELL CCYUSD CostUSD AmountValue DateTrade TypeCUST Side AllocationAmountBUY CCYSELL CCYUSD CostUSD Amount
EURCHF03/01/201706/01/2017SWAPSELL10.00CHFEUR1.0010.0011/01/2017SWAPBUY10.00EURCHF1.0010.00
EURCHF03/01/201706/01/2017SWAPBUY10.00EURCHF1.0010.0011/01/2017SWAPSELL10.00CHFEUR1.0010.00
EURGBP03/01/201701/02/2017SWAPBUY10.00EURGBP1.0010.0004/01/2017SWAPSELL10.00GBPEUR1.0010.00
EURGBP03/01/201704/01/2017SWAPSELL10.00GBPEUR1.0010.0001/02/2017SWAPBUY10.00EURGBP1.0010.00
EURGBP03/01/201731/01/2017SWAPSELL10.00GBPEUR1.0010.0005/01/2017SWAPBUY10.00EURGBP1.0010.00
EURGBP03/01/201731/01/2017SWAPBUY10.00EURGBP1.0010.0005/01/2017SWAPSELL10.00GBPEUR1.0010.00
EURGBP03/01/201711/01/2017SWAPBUY10.00EURGBP1.0010.0006/01/2017SWAPSELL10.00GBPEUR1.0010.00
Message 3 of 7
851 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All