Forum Discussion
Splitting one column in multiple tables based on text in rows containing specified text
I have a simple CSV file, which is updated weekly, adding new data to the file
The file contains agregated data with both time periods; Daily, weekly and monthly downloads. However all the data in placed in Column A, and the Daily, weekly and monthly downloads are just seperated by a row with the time period
How do I split column A, into 3 tables, one for each time period, an get all the daily, weeekly and monthly downloads as seperate tables or columns ?
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,
15 Replies
= Table.Group( your_table, "Column1", {"rows", each _}, GroupKind.Local, (s, c) => Byte.From(Text.Contains(c, "downloads", Comparer.OrdinalIgnoreCase)) )Fowmy Raw table data
Total,"Base Downloads","Unique Downloads"
,"169664","252372"
Daily Downloads,,
2024-01-05,"11","12"
2024-01-04,"37","38"
2024-01-03,"65","66"
2024-01-02,"136","138"
2024-01-01,"48","49"
2023-12-31,"35","38"
2023-12-30,"96","97"
2023-12-29,"89","91"
2023-12-28,"81","82"
2023-12-27,"107","111"
2023-12-26,"97","100"
2023-12-25,"101","103"
Weekly Downloads,,
2024-01-01,"297","303"
2023-12-25,"606","622"
2023-12-18,"2301","2327"
2023-12-11,"283","291"
2023-12-04,"683","694"
2023-11-27,"1340","1360"
2023-11-20,"484","495"
2023-11-13,"1757","1778"
2023-11-06,"372","380"
2023-10-30,"762","803"
2023-10-23,"1660","1680"
2023-10-16,"924","964"
Monthly Downloads,,
2024-01,"297","303"
2023-12,"5064","5140"
2023-11,"3177","3258"
2023-10,"7873","8054"
2023-09,"6470","6765"NorMar
I think, AlienSx has provided a neat and performant solution. I have just used his code and created a PBIX file to demontrate how you can create the required three tables based on the CSV file.
Please find attached the PBIX and the Source file, chagne the file path.
If this works for you, accept the orginal solution provided by AlienSx and if you appreceate my contiribtion, accept mine as well, as a solution.
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?
