Forum Discussion
PowerQuery: Extract Date Into Column | Challenge
Hi Community,
EDIT: Hi MarcelBeug, do you think this is doable? What I'm thinking is to
1. duplicate that column (The one with UNIT ID header)
2. Remove other values other than
| >= 03/01/2017 and < 03/02/2017 |
3. Clean further with final result 03/01/2017
4. Fill Down.
I'm stuck at Step 2 though...
I need some help getting this Date into a Field:
This is how raw data looks like(see desired output just below this one):
| Report Name: | Some_Report_Name | ||
| Site: | Some_Site | ||
| Indicator: | All | ||
| Report Period: | >= 03/01/2017 and < 03/02/2017 | ||
| Employee | Unit ID | Metric 1 | Metric 2 |
| Mraw, Dwana | 907775 | 0 | 0 |
| Mraw, Dwana | 907775 | 0 | 0 |
| Mraw, Dwana | 907775 | 0 | 0 |
| Mraw, Dwana | 907775 | 0 | 0 |
| Makara, Laurena | 909014 | 1 | 594 |
| Makara, Laurena | 909014 | 0 | 0 |
| Makara, Laurena | 909014 | 2 | 707 |
| Makara, Laurena | 909014 | 0 | 0 |
| Kokko, Thurman | 1295855 | 14 | 1175 |
| Spartz, Bradley | 1285484 | 1 | 1103 |
| Spartz, Bradley | 1285484 | 0 | 0 |
| Spartz, Bradley | 1285484 | 51 | 479 |
| Spartz, Bradley | 1285484 | 0 | 0 |
| Setaro, Jerlene | 1296598 | 21 | 616 |
| Erne, Frankie | 1202791 | 0 | 0 |
| Erne, Frankie | 1202791 | 0 | 0 |
| Erne, Frankie | 1202791 | 14 | 523 |
| Erne, Frankie | 1202791 | 0 | 0 |
| Gavell, Elvera | 1296597 | 23 | 599 |
| Langstraat, Miesha | 435099 | 0 | 0 |
| Langstraat, Miesha | 435099 | 0 | 0 |
| Langstraat, Miesha | 435099 | 38 | 511 |
| Langstraat, Miesha | 435099 | 0 | 0 |
| Kanis, Kerri | 1271407 | 1 | 548 |
| Kanis, Kerri | 1271407 | 0 | 0 |
| Kanis, Kerri | 1271407 | 23 | 739 |
| Kanis, Kerri | 1271407 | 0 | 0 |
Here's the Desired Output:
| Employee | Unit ID | Metric 1 | Metric 2 | Date |
| Mraw, Dwana | 907775 | 0 | 0 | 3/1/2017 |
| Mraw, Dwana | 907775 | 0 | 0 | 3/1/2017 |
| Mraw, Dwana | 907775 | 0 | 0 | 3/1/2017 |
| Mraw, Dwana | 907775 | 0 | 0 | 3/1/2017 |
| Makara, Laurena | 909014 | 1 | 594 | 3/1/2017 |
| Makara, Laurena | 909014 | 0 | 0 | 3/1/2017 |
| Makara, Laurena | 909014 | 2 | 707 | 3/1/2017 |
| Makara, Laurena | 909014 | 0 | 0 | 3/1/2017 |
| Kokko, Thurman | 1295855 | 14 | 1175 | 3/1/2017 |
| Spartz, Bradley | 1285484 | 1 | 1103 | 3/1/2017 |
| Spartz, Bradley | 1285484 | 0 | 0 | 3/1/2017 |
| Spartz, Bradley | 1285484 | 51 | 479 | 3/1/2017 |
| Spartz, Bradley | 1285484 | 0 | 0 | 3/1/2017 |
| Setaro, Jerlene | 1296598 | 21 | 616 | 3/1/2017 |
| Erne, Frankie | 1202791 | 0 | 0 | 3/1/2017 |
| Erne, Frankie | 1202791 | 0 | 0 | 3/1/2017 |
| Erne, Frankie | 1202791 | 14 | 523 | 3/1/2017 |
| Erne, Frankie | 1202791 | 0 | 0 | 3/1/2017 |
| Gavell, Elvera | 1296597 | 23 | 599 | 3/1/2017 |
| Langstraat, Miesha | 435099 | 0 | 0 | 3/1/2017 |
| Langstraat, Miesha | 435099 | 0 | 0 | 3/1/2017 |
| Langstraat, Miesha | 435099 | 38 | 511 | 3/1/2017 |
| Langstraat, Miesha | 435099 | 0 | 0 | 3/1/2017 |
| Kanis, Kerri | 1271407 | 1 | 548 | 3/1/2017 |
| Kanis, Kerri | 1271407 | 0 | 0 | 3/1/2017 |
| Kanis, Kerri | 1271407 | 23 | 739 | 3/1/2017 |
| Kanis, Kerri | 1271407 | 0 | 0 | 3/1/2017 |
My approach would be to get the Date, remove rows, promote headers, add a column with the Date and have data types detected (Select all columns - Transform tab - Detect data type).
let
Source = Table1,
Date = Text.Trim(Text.BetweenDelimiters(Source{3}[Column2],">=", "and")),
#"Removed Top Rows" = Table.Skip(Source,4),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Date", each Date),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Employee", type text}, {"Unit ID", Int64.Type}, {"Metric 1", Int64.Type}, {"Metric 2", Int64.Type}, {"Date", type date}})
in
#"Changed Type"
9 Replies
My approach would be to get the Date, remove rows, promote headers, add a column with the Date and have data types detected (Select all columns - Transform tab - Detect data type).
let
Source = Table1,
Date = Text.Trim(Text.BetweenDelimiters(Source{3}[Column2],">=", "and")),
#"Removed Top Rows" = Table.Skip(Source,4),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Date", each Date),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Employee", type text}, {"Unit ID", Int64.Type}, {"Metric 1", Int64.Type}, {"Metric 2", Int64.Type}, {"Date", type date}})
in
#"Changed Type"Your simply awesome!
I'm trying to understand this though:
Source{3}[Column2]Source{3} >>> is that 4th row (0-based index)?
Yes, You get this if you right-click the value and choose Drill Down:
Hi I'm currently in a similar situation except I'm dealing with multiple files that have their dates in that position, I tried this solution but the problem is the date is filled down across all files imported through "Get Folder". Maybe you guys have some ideas how to get the dates of each file applied to a column.
Here's my link:
