Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create DIM Table from Flat Table - Reference or List.Distinct

Hi all,   Sometimes I can only get an extract from the DB which in excel/csv format, so a table with loads of columns. To achieve a better performance and a more understandable data structure I usu...
Need Help
v-chenwuz-msft's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

 

As far as I know, if the data source is excel/csv, power bi will just copy all the data and then clean the data. There is no difference between deleting other columns and keeping only one column. But what I don't understand is how you can create a blank query (I mean a query that sql statement) for a data source that is excel. If it's a blank query but not a sql statement, then it doesn't make any difference.

For a relational database where the data source is sleeping, the second one would be a bit faster, as select [one column] is faster than select *.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thansk for the answer, maybe it was me being lazy to explain. The second option wouold look like this:

let
    DistinctSource = List.Distinct(Table[ColumnName]),
    Convert2Table = Table.FromList(DistinctSource, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Rename = Table.RenameColumns(Convert2Table,{{"Column1", "ColumnName"}}),
    Add_Index = Table.AddIndexColumn(Rename, "ColumnName_ID", 1, 1, Int64.Type)
in
    Add_Index