Forum Discussion
Splitting one column in multiple tables based on text in rows containing specified text
Import your dataset csv to this format and name whole your dataset query as "WholeDataset"
Then create blank query and paste there whole my code. Edit only 1st step to this:
Source = WholeDataset,
You can easily achive this and filter last column as you wish.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZPNasMwEIRfpfhsw/5IK+lacu2tpYckB0MDDQ0xISmlb19ptUmwTHPdT7PWzMjrdfc6XcZDv+mex/PuaTX9HA/T+HHedHn0dtyfvmfDbtuvC0FJIk4PkScOZGg17g+/d0Hf65SA3AA4gC9SVBleJTfo8pSDQo4t5DwVr1CkhVT2sdS9Sy3msYtKXbpDHpAGLpD94qsVQp6mujeFBlLK05gqxBbGAqvTSC0M5Z5QrSIutGKf01PQUs0QLERgw++73dej5ItNsqV8U82WClSnQu2Fsbghto8SU5sF6vrIlS/S0GrFsCQ3w2h5sAMrUKA9ANqgswp9g7E8DgzeIgshNgdA9GmRtTxfD7XmIBXHJhwYSNeL2PVkoUctjOr1klztvUzHy+f/nTwqJM882P/l0TV5FEV2WaXk527VSwxsZvwsbChvVlyoViSIRrn9Aw==", 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"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Total] <> "")),
Ad_DownloadPeriod = Table.AddColumn(#"Filtered Rows", "Download Period", each if List.Contains({"daily downloads", "weekly downloads", "monthly downloads"}, [Total], Comparer.OrdinalIgnoreCase) then [Total] else null, type text),
#"Filled Down" = Table.FillDown(Ad_DownloadPeriod,{"Download Period"}),
#"Filtered Rows1" = Table.SelectRows(#"Filled Down", each not Text.Contains(Text.Lower([Total]), "downloads"))
in
#"Filtered Rows1"
Great, that worked on the sample data, with 33 rows of data. However, when I apply this script upon the master data set, which has +2700 rows ...and counting, I only seem to the results for the sample data set - which is 33 rows
You are doing something wrong. Have you changed first step Source to your dataset? Can you upload your dataset to google drive / one drive and share with us?
It appears that you are referrring to a JSON object, while I have the complete data set as seperate CSV file. I tried to replace the reference to the JSON object, but that backfired 🙂 (Due to co company policy, I have limited option to any file sharing services)
