Forum Discussion
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 | ||
| 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?
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.
4 Replies
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.
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.
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
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.
