Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello I am facing an issue on how to get a measure.
I have a table called tasks.
Each task row has a cell called Details which is fulfilled like this
************************************ 13-Aug-20 1:27 AM @ Person1: ************************************
lorem
************************************ 12-Aug-20 4:44 PM @ Person2 ************************************
ipsum
************************************ 12-Aug-20 6:00 AM @ Person3 ************************************
lorem
************************************ 10-Aug-20 13:27 AM @ Person2 ************************************
ipsum
And I will need to count the times that a Person has written for the last: 7 and 30 days for the whole table.
So I can produce a table like this:
Last 7 days | Last 30 days | |
Person 1 | 1 | 3 |
Person 2 | 4 | 8 |
Person 3 | 3 | 6 |
Splitting by delimiter is not a way as far as n columns may appear so I have no idea on how to filter.
I am thinking and I have no clue on how to do it. Any advice will be fantastic
Thanks in advance
Hi @Chavas ,
Started with pasting your table into a table in Power Query
Then cleaned up the columns to get this:
Is this what you are looking to do? I split columns with delimiters, and replaced values. This is just one way to do it.
If you wish to see my steps go to Power Query, start a blank query, and paste this into the Advanced Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0iICKBga6zqWpusaGSgYWhmZKzj6KjgoBKQWFefnGVopEGOCUqxOtFJOflFqLphFnKVGMEtNrExMFAIQlhoRbyeYyCwoLs1FcEm03czKwADZy8Yk2o7wNgm2G8AD3BgtxEnwPNTfsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.1", Splitter.SplitTextByEachDelimiter({"*"}, QuoteStyle.Csv, true), {"Column1.1.1", "Column1.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.1.1", type text}, {"Column1.1.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type2","*","",Replacer.ReplaceText,{"Column1.2"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Column1.2] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1.1.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.1.2", "Date"}, {"Column1.2", "Name"}})
in
#"Renamed Columns"
It will create a table, and you can follow my steps.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |