Forum Discussion

cavok's avatar
cavok
Frequent Visitor
5 years ago
Solved

Handling CSV with accumulated rows

Hi all,   we are a Microsoft Partner and want to analyse the Azure Costs of our customers that have a CSP subscription from us. For this purpose our distributor provides us a CSV export in our blob...
Need Help
  • cavok's avatar
    cavok
    5 years ago

    Problem is solved... thank you very much for your fast help.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi cavok 

 

When you imported the data to the "BINARIES" level, do you mean you do something like this?

 

Table.PromoteHeaders( Csv.Document([Content]))

 

Then you combin tables together, it looks like you have duplicated rows, remove them should be working:

 

Table.Distinct( Table.Combine( #"Added Custom"[Custom]))

  • cavok's avatar
    cavok
    Frequent Visitor
    5 years ago

    Hi Anonymous ,

    after adding the blob storage folder i've seen the file level in PowerQuery. The only thing I did was a "Click" on the "CONTENT ICON" in the binary column (see snip).


    I do not join any tables. It is only one big table. The advanced editor has this code after clicking the "CONTENT ICON":

     

    But yes I think the solution would be, deleting all rows that have the same data in every column. How can i manipulate the import with a DISTINCT so that these rows aren't imported?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi cavok 

       

      Do not click the botton when you are at the snip, add a custom column instead, 

       

      Go to Add Coumn, then put Table.PromoteHeaders( Csv.Document([Content])), you will see a table for each .csv in this new column

       

       

      Then go to Applied steps, right click to "Insert Step After" with the code: Table.Distinct( Table.Combine( #"Added Custom"[Custom]))

      • cavok's avatar
        cavok
        Frequent Visitor
        5 years ago

        Hi Anonymous ,

        i think we're on the right way - tried a different solution, that worked, but doubled the amound data 😄 - I added a new column which contains every data of every column in one string. After that i clicked "delete duplicate rows". Don't think that this is the best solution.

        Tried your way, but I can't insert the "Distinct Code":