Forum Discussion

Lenihan's avatar
Lenihan
Icon for Helper III rankHelper III
9 years ago
Solved

append data from two columns only for distinct count.

Hi,  So I have a table of about 25 columns, 35K rows of data. I want to take two of those columns, append the data to make once column of about 70K of data. Then, remove duplicates.   Would this ...
Need Help
  • Lenihan's avatar
    Lenihan
    9 years ago

    Thank you Imke for your patience and assistance. I was finally able to get it using the old copy/paste/paste/merge/remove duplicates way. I was able to get the chart right too - apparently I had the relationship pointing the wrong way. Once I changed the direction, it is now displaying the right values.

Lenihan's avatar
Lenihan
Icon for Helper III rankHelper III
9 years ago

I'm still having challenges..  I right clicked, selected Reference. Entered the formula, but got that error.  I tried without the quotes as well, but that errors too.

 

I've also never used powerpivot or powerquery, so the blog post link from Matt didn't help me . sorry. I'm new too all this.. Excel functions and formulas are what I was only ever used to. Learning lots though!

 

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
9 years ago

It looks as if you are trying to modify the first step of your new query instead of creating a second one like I tried to suggest.

 

But never mind, you can do it like that as well. You just have to use a special syntax in order to reference elements in M, who contain special characters, numbers or blanks: Wrap them in #"....".

 

So: #"CI Relationship Report" in your case.

  • Lenihan's avatar
    Lenihan
    Icon for Helper III rankHelper III
    9 years ago

    Sorry, I didn't mean to modify the first step. I must have misunderstood.

     

    I followed these steps this time then (hopefully did it as was expected)

     

    Open the query editor, right click then click Reference then clicked on the Fx, pasted the formula.


    This time no error - but it just keeps processing. In the bottom right corner, it counts the mb usage from the table itself. The filesize on the table that it is processing is only 9.5 MB; however, this formula was processing over 300 MB in size before I clicked on cancel.

    I tried pasting this formula
    List.Count(List.Distinct(List.Union(Table.ToColumns(Source))))

     

    and this one

     

    List.Count(List.Distinct(List.Union(Table.ToColumns(#"CI Relationship Report))))

     

    But neither will finish processing. They keep running.

     

  • Lenihan's avatar
    Lenihan
    Icon for Helper III rankHelper III
    9 years ago

    So I'm really starting to pull my hair out here.. I've spent over 30 hours for just one chart of about 40 I need.. 

     

    I don't know if it is a relationship issue between my tables. Instead of trying to do count the unique values as a formula, i decided to a brute force solution - copy the data tables, delete all tables except the ones I want to stack, rename them then append them. It gave me the number I need.  I then added a column called "Reporting Month" and set it to January 2017.

     

    So.. I'm trying to use that number now in a formula (Measure)

     

    No Relationship % = (count('All CIs'[CI Identifier]) - COUNT('UniqueCIRelationships'[All CI Identifiers])) / COUNT('All CIs'[CI Identifier])

     

    ALL Ci's is my main table. It has a list of all CIs that exist in the database

    UniqueCIRelationships is the table i created with only those CIs that have a known relationship to other CIs. This is the one I appended then deleted duplicates.

     

    If i do a count of (count('All CIs'[CI Identifier]) it equals 51,010  (good)

    If I do a count of COUNT('UniqueCIRelationships'[All CI Identifiers])), it comes out to 23,466  (good)

    The math for Relationship % comes out to 53%. Good there too

     

    Now I try to put that in a table, I add the column "Reporting Month" which is a column in every table I have. They all are equal to January 2017 right now. The visual displays a percentage of -26.7%  ??  When I check the total of All CIs - Unique CIs, it comes out to a negative number on a visual if I plot it against the Reporting month column..but without adding that month and just making a card, the value is right.  

     

    And unfortunately I'm not able to share my data so I'm unsure where to go next. 

  • Lenihan's avatar
    Lenihan
    Icon for Helper III rankHelper III
    9 years ago

    Thank you Imke for your patience and assistance. I was finally able to get it using the old copy/paste/paste/merge/remove duplicates way. I was able to get the chart right too - apparently I had the relationship pointing the wrong way. Once I changed the direction, it is now displaying the right values.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Lenihan,

     

    Great to hear the problem got resolved! Could you accept the corresponding reply as solution to help others who may have similar issue easily find the answer and close this thread?:smileyhappy:

     

    Regards

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Sorry Lenihan to see that the performance of my formula is so poor.

    This was a surprise for me as well and I tried to speed it up with no success. M has some unbuilt algorithms that might cause the source to be hit multiple times and there is nothing we users can do about it at the moment (even buffer doesn't work).

    But I must admit that I've never come across sth bad like this... :-(