Forum Discussion
Solved
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/20...
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!