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 un...
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.
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.