Create a portfolio table from a buy and sell table

[Adoman]

Hi All,

I have the following data that I put in to the table:

PortfolioID	SellerID	BuyerID
1111	5553	6112
1111	2118	5553
1123	5559	6088
1828	6169	3509;5375
2494	1672;6057	4062
2961	5649	5648
2500		4726
2653	6112	5026
1111	5553;6112	1433

This table contains Companies (PortfolioID) that are sold or bought by a PE Houses (Seller and Buyer). I would like to create a Portfolio table which contains only Buyers who did not sell a company and holds it. E.g.: the company 1111 should not be shown in portfolio of the PE House 6112. (bought in the first row and sold in the last row).

Any ideas how can I solved this Power Bi?

Thx

[Eric_Zhang · Accepted solution]

@Adoman

At the very beginning, I think you'll have to re-model the data.

let
    Source = Table.FromRows({{1111 ,"5553", "6112"}, 
{1111 ,"2118", "5553"}, 
{1123 ,"5559", "6088"},
{1828 ,"6169", "3509;5375"}, 
{2494 ,"1672;6057", "4062"}, 
{2961 ,"5649", "5648"}, 
{2500 ,"","4726"},
{2653 ,"6112", "5026"}, 
{1111 ,"5553;6112", "1433"}},
{"PortfolioID", "SellerID" ,"BuyerID" } 
),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index","SellerID",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"SellerID.1", "SellerID.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter","BuyerID",Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv),{"BuyerID.1", "BuyerID.2"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"PortfolioID", "SellerID.1", "SellerID.2", "Index"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"PortfolioID", "Index", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns1",{{"Value.1", "SellerID"}, {"Value", "BuyerID"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.1", "Attribute", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"PortfolioID", "SellerID", "BuyerID"})
in
    #"Reordered Columns"

Then a calculated table as

Table =
SUMMARIZE (
    FILTER (
        're-model table',
        IF (
            CONTAINS (
                FILTER (
                    CROSSJOIN (
                        SELECTCOLUMNS (
                            're-model table',
                            "PortfolioID", 're-model table'[PortfolioID],
                            "buyerid", 're-model table'[BuyerID],
                            "sellerid", 're-model table'[SellerID]
                        ),
                        SELECTCOLUMNS (
                            're-model table',
                            "PortfolioID2", 're-model table'[PortfolioID],
                            "buyerid2", 're-model table'[BuyerID],
                            "sellerid2", 're-model table'[SellerID]
                        )
                    ),
                    [PortfolioID] = [PortfolioID2]
                        && ( [buyerid] = [sellerid2]
                        || [sellerid] = [buyerid2] )
                ),
                [PortfolioID], 're-model table'[PortfolioID]
            ),
            FALSE (),
            TRUE ()
        )
    ),
    're-model table'[PortfolioID]
)

[Adoman]

Thanks a lot, i really appreciate it.