Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

measure exploding dimensins

Hi

 

This is my table. The formatting I require, all is good.

 

Now I run, from my cube, [..]AddMeasureColumn([...]"measure C" and my fields sort of "explodes". I have some guesses to why measure C is written in such a way, but how can I maintain the table format in the top image? 

Question A: Can I disable the "explosion" somehow? Such that adding measure C only adds 1 column and nothing else

Question B: Given the answer to A is no, what is the best practise workaround?

Edit: Before adding measure C, my table contains ~2.5m rows.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    After importing, 

    sort "delivery" on ascending firstly, sort "street" ascending secondly,

    select [Meaure C], "Transform->fill down",

    then filter [measure A] <> null.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwUNJRUgZiCDJUitWBiXq4KpgAKWMTYxNTE1MgyxQkClVgiKLNCEk0KEQBpNjE1NTEzBRkgBlEWywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [delivery = _t, street = _t, #"measure A" = _t, #"measure B" = _t, #"measure C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"delivery", Int64.Type}, {"street", type text}, {"measure A", Int64.Type}, {"measure B", Int64.Type}, {"measure C", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"delivery", Order.Ascending}, {"street", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"measure C"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([measure A] <> null))
in
    #"Filtered Rows"

    Regarding performance issue, please check the ways to improve performance.

    Performance Tip for Power BI; Enable Load Sucks Memory Up

    Performance tip for List.Generate (1): Buffer your tables in Power BI and Power Query

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    Do you import data or use live connection/direct query?

    if you could use Edit queries, you can select the [Measure C] column, then select option "fill down",

    then select [Measure A], select "remove blank(empty)".

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    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
      6 years ago
      Thank you for your answer. If my table is not sorted like shown, I will then need to sort it to use this operation correctly. Is it then the best approach in terms of performance?

      Not using direct query.
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi Anonymous 

    After importing, 

    sort "delivery" on ascending firstly, sort "street" ascending secondly,

    select [Meaure C], "Transform->fill down",

    then filter [measure A] <> null.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjAwUNJRUgZiCDJUitWBiXq4KpgAKWMTYxNTE1MgyxQkClVgiKLNCEk0KEQBpNjE1NTEzBRkgBlEWywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [delivery = _t, street = _t, #"measure A" = _t, #"measure B" = _t, #"measure C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"delivery", Int64.Type}, {"street", type text}, {"measure A", Int64.Type}, {"measure B", Int64.Type}, {"measure C", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"delivery", Order.Ascending}, {"street", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"measure C"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([measure A] <> null))
in
    #"Filtered Rows"

    Regarding performance issue, please check the ways to improve performance.

    Performance Tip for Power BI; Enable Load Sucks Memory Up

    Performance tip for List.Generate (1): Buffer your tables in Power BI and Power Query

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.