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.
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 |
Solved! Go to Solution.
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"
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:
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:
Sorry @MarcelBeug,
Kind of messed up:
let #"Transform Sample File from CRO_Data" = let Source = Excel.Workbook(#"Sample File Parameter1", null, true), #"Call_Stats_by_Specialist_by_FA _Sheet" = Source{[Item="Call_Stats_by_Specialist_by_FA ",Kind="Sheet"]}[Data], Date = Text.Trim(Text.BetweenDelimiters(#"Call_Stats_by_Specialist_by_FA _Sheet"{3}[Column2],">=", "and")) in #"Call_Stats_by_Specialist_by_FA _Sheet", #"Filtered Rows" = Table.SelectRows(#"Transform Sample File from CRO_Data", each not Text.Contains([Column1], ":")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Date", each Date) in #"Added Custom"
What did I do wrong this time?
Okay, I manually moved things around and came up with this:
let #"Transform Sample File from CRO_Data" = let Source = Excel.Workbook(#"Sample File Parameter1", null, true), #"Call_Stats_by_Specialist_by_FA _Sheet" = Source{[Item="Call_Stats_by_Specialist_by_FA ",Kind="Sheet"]}[Data], Date = Text.Trim(Text.BetweenDelimiters(#"Call_Stats_by_Specialist_by_FA _Sheet"{3}[Column2],">=", "and")), #"Added Custom" = Table.AddColumn(#"Call_Stats_by_Specialist_by_FA _Sheet", "Date", each Date) in #"Added Custom", #"Filtered Rows" = Table.SelectRows(#"Transform Sample File from CRO_Data", each not Text.Contains([Column1], ":")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]) in #"Promoted Headers"
It worked.... checking if I could further simplify...
Tossed things around a bit, and I guess here's the final form, just like what you say how it should be done:
let Source = Excel.Workbook(#"Sample File Parameter1", null, true), #"Call_Stats_by_Specialist_by_FA _Sheet" = Source{[Item="Call_Stats_by_Specialist_by_FA ",Kind="Sheet"]}[Data], Date = Text.Trim(Text.BetweenDelimiters(#"Call_Stats_by_Specialist_by_FA _Sheet"{3}[Column2],">=", "and")), #"Filtered Rows" = Table.SelectRows(#"Call_Stats_by_Specialist_by_FA _Sheet", each not Text.Contains([Column1], ":")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Date", each Date) in #"Added Custom"
Thanks again @MarcelBeug for showing the way!
Yes, that's better without the inner "let .. in".
One thing I would adjust, i.e. the step #"Filtered Rows" in which you filter out the first rows (the ones containing a ":").
#"Filtered Rows" = Table.Skip(#"Call_Stats_by_Specialist_by_FA _Sheet",each Text.Contains([Column1],":")),
With this adjustment, only the first rows containing a ":" will be removed, not any subsequent rows that might contain a ":", so this is safer.
Syntax for Table.Skip is:
Table.Skip(table as table, countOrCondition as any) as table
In this case, I use the second parameter as a condition.
Thanks for sharing a best practice... Advise taken and applied!
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |