Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Combining multiple queries on the same file into a single query

I have a CSV file format that looks something like this:   Title I Need   8   POINTS 13   POINTS       Lorem ipsum   dolor sit amet   consectetur adipiscing elit un...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can create POINTS as a custom column like this:

     

    let
    Source = Csv.Document(File.Contents("C:\Path\To my\file.csv"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Columns",1),
    #"Renamed Columns" = Table.RenameColumns(#"Kept First Rows",{{"Column1", "TITLE"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "POINTS", each List.Sum(#"Changed Type"[Column3]), Int64.Type)
in
    #"Added Custom"

     

    This way you only need one query.

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

You can create POINTS as a custom column like this:

 

let
    Source = Csv.Document(File.Contents("C:\Path\To my\file.csv"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Columns",1),
    #"Renamed Columns" = Table.RenameColumns(#"Kept First Rows",{{"Column1", "TITLE"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "POINTS", each List.Sum(#"Changed Type"[Column3]), Int64.Type)
in
    #"Added Custom"

 

This way you only need one query.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This led to the solution, thanks!

     

    let
    Source = Csv.Document(File.Contents("C:\path\To my\file.csv"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Column3] = "POINTS" then [Column1] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column2", "Column3", "Custom"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Columns",1),
    #"Renamed Columns" = Table.RenameColumns(#"Kept First Rows",{{"Column1", "TITLE"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "POINTS", each List.Sum(#"Changed Type"[Custom]))
in
    #"Added Custom"

     

    I changed it a bit.  Yours had the third column converted to number format, but it is the text label, so that didn't work.  So I added a new conditional column which copied the values from column 1, if column 3 had the label I want.  Then I kept the first row, and created the new column with the sum of the copied conditional column.

     

    Thanks.