Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Adding a column or table with skipped rows in Desktop

Hi again!

 

 I was able to do adapt a solution I got from an user here using Power Query, but it turns out that the actual file is really heavy, so it takes way too much time to load, so now I'm trying to replicate it in- Desktop with Dax.

 

 

 I'm trying to get which rows are getting skipped and I would like to add them to a new column or a new table. For example, in the new column on the 2nd row should say "2232-2233-2234-2235-2236".

 

I tried using a while loop but I'm missing how to actually write different values in the same row of the new column.

I'd appreciate any advice.

 

Thanks in advance for any help!

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    You can try the below, but it may not perform well on your large dataset

    Missing Values = 
var currentValue = 'Table'[Nro.Comprob.]
var prevValue = SELECTCOLUMNS( TOPN(1, FILTER( ALL('Table'), 'Table'[Nro.Comprob.] < currentValue), 'Table'[Index], DESC), "@val", 'Table'[Nro.Comprob.])
return IF( 'Table'[Index] > 1, CONCATENATEX( GENERATESERIES( prevValue + 1, currentValue - 1), [Value], "-"))

    The CONCATENATEX .. GENERATESERIES principle should work fine, but you may need a more performant way of getting the previous value.

7 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    You can try the below, but it may not perform well on your large dataset

    Missing Values = 
var currentValue = 'Table'[Nro.Comprob.]
var prevValue = SELECTCOLUMNS( TOPN(1, FILTER( ALL('Table'), 'Table'[Nro.Comprob.] < currentValue), 'Table'[Index], DESC), "@val", 'Table'[Nro.Comprob.])
return IF( 'Table'[Index] > 1, CONCATENATEX( GENERATESERIES( prevValue + 1, currentValue - 1), [Value], "-"))

    The CONCATENATEX .. GENERATESERIES principle should work fine, but you may need a more performant way of getting the previous value.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      johnt75 

      Sorry, just one more question about your solution.
      I replaced the way of getting the previous value by just adding a new column with that previous value, so it would be something like:

      var currentValue = 'Table'[Nro.Comprob.]
var prevValue = 'Table'[PreviousValue]

      So it doesn't have to do all the calculation. However, I still getting the memory usage issue. Is there a possibility that in this case the data set is too large, no matter what function is being used?

       

      Thanks again.

       

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        4 years ago

        It sounds like it is. The only other thing I can think of is to try limiting the data you load into Power BI Desktop by using Power Query parameters and then try publishing to the service and load in all the data, see if the service can handle it. Don't know whether that would work in your circumstances

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       Yes, indeed it didn't perform well when I tried it. The result doesn't even show up because memory isn't enough haha But I think it's a problem with my data being so large, not the code. I'll try to think a different aproach.

       

      Thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I tried to apply it but it didn't seem to solve my problem. Thank you anyway. I didn't know about that tool and I think it will be useful in the future.