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 usually try to transform this table into a "starschema". I often face a situation when I only have 1 column in the source table which later can be expanded into multiple columns (imagine a format soemthing like "Category - SubCategory - Name" in 1 cell). I was wondering which of the 2 options I have in mind is better from a performance point of view - if there's any difference at all - to extract that 1 specific column from the table.

 

1) So one of the options to create the "Dim" table is just referencing the original table remove the unnecessary columns > remove duplicates >add ID --> ready.

2) The other option is to create a blank query where we use the List.Distinct() function on the column > Convert to Table > Add ID --> Ready

 

Do you think there is any significant diference between these 2 methods? Is one more recommended than the other?

 

Thanks! 

Need Help

2 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community 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