Create a portfolio table from a buy and sell table

Question

Hi All,&nbsp;I have the following data that I put in to the table:PortfolioIDSellerIDBuyerID111155536112111121185553112355596088182861693509;537524941672;605740622961564956482500&nbsp;472626536112502611115553;61121433This table contains Companies (PortfolioID) that are sold or&nbsp;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).&nbsp;Any ideas how can I solved this Power Bi?&nbsp;Thx

eric_zhang · Accepted Answer

Adoman
&nbsp;
At the very beginning, I think you'll have to re-model the data.
&nbsp;
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"

&nbsp;
&nbsp;
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]
                        &amp;&amp; ( [buyerid] = [sellerid2]
                        || [sellerid] = [buyerid2] )
                ),
                [PortfolioID], 're-model table'[PortfolioID]
            ),
            FALSE (),
            TRUE ()
        )
    ),
    're-model table'[PortfolioID]
)

&nbsp;

Forum Discussion

Create a portfolio table from a buy and sell table

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Create a work portfolio

Creating Date Tables

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