Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

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  
EmployeeUnit IDMetric 1Metric 2
Mraw, Dwana90777500
Mraw, Dwana90777500
Mraw, Dwana90777500
Mraw, Dwana90777500
Makara, Laurena9090141594
Makara, Laurena90901400
Makara, Laurena9090142707
Makara, Laurena90901400
Kokko, Thurman1295855141175
Spartz, Bradley128548411103
Spartz, Bradley128548400
Spartz, Bradley128548451479
Spartz, Bradley128548400
Setaro, Jerlene129659821616
Erne, Frankie120279100
Erne, Frankie120279100
Erne, Frankie120279114523
Erne, Frankie120279100
Gavell, Elvera129659723599
Langstraat, Miesha43509900
Langstraat, Miesha43509900
Langstraat, Miesha43509938511
Langstraat, Miesha43509900
Kanis, Kerri12714071548
Kanis, Kerri127140700
Kanis, Kerri127140723739
Kanis, Kerri127140700

 

Here's the Desired Output:

EmployeeUnit IDMetric 1Metric 2Date
Mraw, Dwana907775003/1/2017
Mraw, Dwana907775003/1/2017
Mraw, Dwana907775003/1/2017
Mraw, Dwana907775003/1/2017
Makara, Laurena90901415943/1/2017
Makara, Laurena909014003/1/2017
Makara, Laurena90901427073/1/2017
Makara, Laurena909014003/1/2017
Kokko, Thurman12958551411753/1/2017
Spartz, Bradley1285484111033/1/2017
Spartz, Bradley1285484003/1/2017
Spartz, Bradley1285484514793/1/2017
Spartz, Bradley1285484003/1/2017
Setaro, Jerlene1296598216163/1/2017
Erne, Frankie1202791003/1/2017
Erne, Frankie1202791003/1/2017
Erne, Frankie1202791145233/1/2017
Erne, Frankie1202791003/1/2017
Gavell, Elvera1296597235993/1/2017
Langstraat, Miesha435099003/1/2017
Langstraat, Miesha435099003/1/2017
Langstraat, Miesha435099385113/1/2017
Langstraat, Miesha435099003/1/2017
Kanis, Kerri127140715483/1/2017
Kanis, Kerri1271407003/1/2017
Kanis, Kerri1271407237393/1/2017
Kanis, Kerri1271407003/1/2017
1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
Xandman
Helper II
Helper II

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:

https://community.powerbi.com/t5/Desktop/Extracting-Date-and-Converting-to-a-Column/m-p/1734550#M683...

MarcelBeug
Community Champion
Community Champion

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"

 

Specializing in Power Query Formula Language (M)

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:

 

DrillDown.png

Specializing in Power Query Formula Language (M)

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"

GetThatDate.PNG

 

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.

 

Specializing in Power Query Formula Language (M)

Thanks for sharing a best practice... Advise taken and applied!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.