Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Continuously compiling excel data source?

Hi there,   We have salespeople generate a quote anytime there's a new prospected client. This quote is then saved in a folder as an Excel file.   The formatting of the quote excel files are all ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Ahh yes! Okay, so I was able to use the "combine files in folder" source setting and every time I refresh my dataset it updates. Amazing thank you! 

 

Now I am left with another issue I haven't dealt with yet.

When the data is brought in the column names and data continue down the rows. For Example:

I have 4 columns in the Quoted excel sheet: First Name, Last Name, Quoted Amount, Date.

Lets say there are 2 files brought in. This is how it would look after it's brought into Power BI:

If I transpose the data it will then look like this which is not very useful:

 

How do I transpose the data so the columns don't duplicate like below:

 

Thank you!

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    The issue I am having with that is it's just counting the number of cells in each category rather than pasting the actual value.

     

    Also, if I select "Don't Aggregate" I get these errors

     

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      Ah, right.

       

      The issue here is that without some sort of indexing, it doesn't know that the first four rows belong together in one row and the last four rows belong in another.

       

      You can fix that by adding an appropriate index:

       

      Here's an example of you you can define this assuming you've got a consistent number of rows per chunk:

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsssKi5R8EvMTVXSUXJJzFOK1YlW8klEiAXnZuaUZICFA0vzS1JTFBxz80vzSoBShgYGBmAJl8QSkFJTfUMjfSMDIyOwIFAATKPY4JxRlFmMYUdAfk5OfnI2dkuMTNEtMYRaEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Col2] <> "")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Col1]), "Col1", "Col2")
in
    #"Pivoted Column"

       

      Result: