Forum Discussion

Danielwood's avatar
Danielwood
Helper I
3 years ago
Solved

PBI Table Creation

Hi,

 

Is there a way in PBI to use my main data source to then create a sub table?

 

My main table has lots of duplicates (a necessary evil), but has a reference, plus a type column. What I'm hoping is to use that main table to then pull in all the references and types, remove the duplicates in the references column, then this data can feed my visual.

 

Is that possible?

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Danielwood ,

     

    Please try:

    In PowerQuery Editor --> Right-click the source table -->  Reference the Table.

    For the new table --> select the [Reference] and [Type] columns --> Right-click and select the remove other columns.

    then remove duplicates.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

3 Replies

  • kpost's avatar
    kpost
    Solution Sage
    3 years ago

    Can you please explain in more detail what you mean by "pull in all the references and types" and "create a sub table"?

    There are many ways to make new tables from existing data within Power BI, either within Power Query or using DAX depending on the situation.  Please flesh out your idea a bit more so we know exactly how to help.

    • Danielwood's avatar
      Danielwood
      Helper I
      3 years ago

      Hi, sorry for not giving enough information. 

       

      So my main data sheet has about 60 columns, and 300+ rows of data. Due to the nature of the data, the source excel spreadsheet has LOTS of duplicate rows.

       

      There is two columns though which I need copied into a separate table. The first column is called Reference, the second is called Type.

       

      The data in the Type column falls into 8 possible entries.

       

      The reference column has reference values, some of which are duplicated due to the source data. For every reference though, it should have one type, even across the duplicate rows. 

       

      What I'm hoping for is to set a table for the reference and type, but removing the duplicated reference rows. 

       

      I'm then going to use this in a visual, which is a barc graph of the number of types. The visual currently double counts the types though, so if one reference is type Apple, but the reference is repeated 6 times, the visual counts 6 apples.

       

      Hopefully that explains more. Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Danielwood ,

     

    Please try:

    In PowerQuery Editor --> Right-click the source table -->  Reference the Table.

    For the new table --> select the [Reference] and [Type] columns --> Right-click and select the remove other columns.

    then remove duplicates.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum