Combining multiple queries on the same file into a single query

[Anonymous]

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
unc ac sem lorem. Quisque diam orci, posuere eget erat nec, elementum varius
3		POINTS
diam
Nunc nisi orci,

The top line is always a title that I need to keep in column 1 - there is only one column of data in this row.  There are then a variable number of rows.  Most rows I can discard, but some rows have numbers in column 1, and the label "POINTS" in column 3.  I need to report the title and sum of the points in the format:

Title I Need

24

I do this with three steps:

1) I create a query that opens the CSV file, keeps the first row and discards all columns except the first.

let
    Source = Csv.Document(File.Contents("C:\path\to my\file.csv"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column2", "Column3"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Columns",1),
    #"Renamed Columns" = Table.RenameColumns(#"Kept First Rows",{{"Column1", "TITLE"}})
in
    #"Renamed Columns"

- The result is a table:

TITLE

Title I Need

2) I create a query that opens the same CSV file, selects (filters) for rows that have "POINTS" in column 3, then SUMs column 1 and returns it as a table.

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", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column3] = "POINTS")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column2", "Column3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", Int64.Type}}),
    #"Calculated Sum" = List.Sum(#"Changed Type1"[Column1]),
    #"Converted to Table" = #table(1, {{#"Calculated Sum"}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "POINTS"}})
in
    #"Renamed Columns"

- The result is a table:

POINTS

24

3) I create a query that appends the second table to the first, fills the first column down, then removes row 1. 

let
    Source = #"Title Query - Src1",
    #"Appended Query" = Table.Combine({Source, #"Points Query - Src1"}),
    #"Filled Down" = Table.FillDown(#"Appended Query",{"TITLE"}),
    #"Removed Top Rows" = Table.Skip(#"Filled Down",1)
in
    #"Removed Top Rows"

- The result is a table:

TITLE	POINTS
Title I Need	24

Is there any way I can combine these into a single query?  Or should I be content with these three?

[mahoneypat]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsksyUlV8FTwS01NUdJRUorViVayADIC/D39QoLBXENjJH4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column2] = "" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column2] = "POINTS")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Column1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Custom"}, {{"Points", each List.Sum([Column1]), type nullable text}})
in
    #"Grouped Rows"

Pat

[Anonymous]

Unfortunately, this doesn't work.  It will work for the sample data you provided, but that data doesn't have intermediate text in columnn 1 (the lorem ipsum text in the example I provided).  With the intermediat text, the new column will get populated with incorrect values.  For example, with the sample data I provided, the output would be:

TITLE	POINTS
Title I Need	21
unc ac sem lorem. Quisque diam orci, posuere eget erat nec, elementum varius	3

I wasn't able to come up with a way of just copying the first row's value over to the new column.

[AlexisOlson · Accepted solution]

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]

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.