PBI Table Creation

[Danielwood]

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

[v-cgao-msft · Accepted solution]

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

[kpost]

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]

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