Forum Discussion

FlyKick's avatar
FlyKick
Helper II
4 years ago
Solved

Handling CSV With Different Transaction Types

Hi All,

Looking for recommendations on how to best manage importing a CSV that contains different transaction types. Here is a quick sample of how the CSV looks.

 

Standard Orders

Name, OrderNumber, OrderDate

Bob, 123456, 23/09/2021

Jane, 987654, 24/09/2021

 

Premium Orders 

Name, OrderNumber, OrderDate

Jack, 234567, 22/09/2021

Sally, 875903, 23/09/2021 

 

As you can see the CSV starts with an identifier for the type of transaction it then lists the headers for the CSV and then the records. I am comfortable removing the blank lines and the headers. I am not 100% sure how to go about creating a new column to hold the transaction type? 

 

Ideally I want to get the data set like this 

 

Name, OrderNumber, OrderDate, TransactionType

Bob, 123456, 23/09/2021, Standard Orders

Jane, 987654, 24/09/2021, Standard Orders

 

Name, OrderNumber, OrderDate, TransactionType

Jack, 234567, 22/09/2021, Premium Orders 

Sally, 875903, 23/09/2021, Premium Orders 

 

I can find the transaction type using 

if [Name] = "Standard Orders" then "" else "")

 

I tried creating a paramater and then changing it's value using the if statement above but it doesn't look like thats allowed. I then thought I might need to create a function and set a variable then put the if statement inside the function and return the value but it doesn't seem you can update variables either. 

 

I think I am pretty close but just need someone to point me in the right direction. Appreciate any tips or pointers. 

 

Cheers,

Fly Kick 

Need Help
  • KNP's avatar
    KNP
    4 years ago

    Hi FlyKick,

     

    Not sure I fully understand the format of your file but paste the below into the advanced editor of a blank query and let me know if I'm on the right track.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JzEtJLEpR8C9KSS0qVorViVbyS8xN1YEI+JXmJqUWQTkuiSWpYAVO+Uk6CoZGxiamZjoKRsb6Bpb6RgZGhmA5r8Q8oGZLC3MzUxOgpAmKZEBRam5maS7UMgXibPNKTM4GWQO0zRxIG6GYGJyYk1Opo2BhbmppYIzsGKDhsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Column1.2] = null then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
    #"Filled Down"

     

    Regards,

    Kim

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    You can add a column (using the custom column dialog)that retrieves the first item in the column like this:

    #"Changed Type"[Column1]{0}

    where #"Changed Type" is the name of the step (or table)

    • FlyKick's avatar
      FlyKick
      Helper II
      4 years ago

      Hi Hot Chilli,

      Thanks for the response, unfortunately that will fill all the rows with the Standard Orders even the premium rows. I am looking for a way to fill all rows with standard order until it gets to the next transaction type header.

       

      That's why I thought it needs a variable or parameter. Something that can be updated based on the value of the last row.

       

      in pseudo code it would be

      if [name]{[index]-1 = "Standard Orders" then set columnNameVar to "Standard Orders" else 

      if [name]{[index]-1 = "Premium Orders" then set columnNameVar to "Premium Orders" else

      dont update the variable name

       

      Then I would add a conditional column based on the variable name. Not sure if this possible in Power Query though?

       

      Might end up using a logic app to process the csv and add the records to azure table storage and then connect PBI to that. 

       

      • KNP's avatar
        KNP
        Super User
        4 years ago

        Hi FlyKick,

         

        Not sure I fully understand the format of your file but paste the below into the advanced editor of a blank query and let me know if I'm on the right track.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5JzEtJLEpR8C9KSS0qVorViVbyS8xN1YEI+JXmJqUWQTkuiSWpYAVO+Uk6CoZGxiamZjoKRsb6Bpb6RgZGhmA5r8Q8oGZLC3MzUxOgpAmKZEBRam5maS7UMgXibPNKTM4GWQO0zRxIG6GYGJyYk1Opo2BhbmppYIzsGKDhsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Column1.2] = null then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
    #"Filled Down"

         

        Regards,

        Kim