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
Anonymous
Not applicable

Extracting a date from a cell to a convert to a column

Hi all,

 

I am new to power query but hoping someone can help out with this one. I have the raw data excel file and need to add date as a column for each row based on the text in a cell. I have monthly data table files but the date as a text is on top of the file (2nd row) and not a column. 

srzchem_0-1650582056634.png

 

For example - the cell says 1/1/2021 - 1/312021. I need to extract 1/31/2021 (after delimiter "-") and add it as a column to my table. I would need to replicate it for Jan 2021 - Current (15 months of data) as I append the tables in my data model. I hae a calendar table in my data model that will join to this date. Hoping someone can help with this.

 

thanks!

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

try to use this function with your tables and let's know

 

 

 let yourFunction = (yourTab) => let Report_Sheet = yourTab{[Item="Report",Kind="Sheet"]}[Data], #"Removed Top Rows" = Table.Skip(Report_Sheet,1), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column6", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column6.1", "Column6.2"}), #"Filled Down" = Table.FillDown(#"Split Column by Delimiter",{"Column6.2"}), #"Removed Top Rows1" = Table.Skip(#"Filled Down",4), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{" 1/31/2021", "Report Effective Date"}}) in #"Renamed Columns" in yourFunction 

 

 

a problem could be that of making the rename of the new column "Report Effective Date" dynamic from the splitted date.
You should try to find a rule that always applies:
1) is it the last column of the table?
2) is it in a precise position of the table [12 position]?
3) 3) is it immediately after a column that has a well-defined name?
4) another rule ...

 


 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @PhilipTreacy 

 

the new column of dates should have the same date for each row. for now, I am doing the following steps to create output in this format.

 

srzchem_0-1650634614776.png

This is my desired result,

 

srzchem_1-1650634698121.png

 

I am hoping for a more elegant solution that can save me sometime since I need to replicate it for multiple files.

 

regards,

Shweta

 

Anonymous
Not applicable

If the files you need to transform have the same structure as the first one on which you performed the sequence of operations, you could define a function that contains these transformations and that has a new table as input parameter from time to time.

It starts with Navigation and ends with renamed columns.

Make a duplicate of the query that contains these transformations and copy and paste it into the forum.

I'll show you how to turn it into a function that you can call up when needed.

 

PS

do not paste as image!

paste a text that is easily copied!

Anonymous
Not applicable

Hi @Anonymous 

 

Thank you for your reply. 

 

Here is the query:

 

let
Source = Excel.Workbook(File.Contents("C:\Users\OneDrive\Shweta\Overtime & Fatigue\WD Payroll Include Corrections_Jan 2021.xlsx"), null, true),
Report_Sheet = Source{[Item="Report",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(Report_Sheet,1),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column6", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column6.1", "Column6.2"}),
#"Filled Down" = Table.FillDown(#"Split Column by Delimiter",{"Column6.2"}),
#"Removed Top Rows1" = Table.Skip(#"Filled Down",4),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{" 1/31/2021", "Report Effective Date"}})
in
#"Renamed Columns"

 

Let me know if this works for you.

 

Regards

Shweta

Anonymous
Not applicable

 

try to use this function with your tables and let's know

 

 

 let yourFunction = (yourTab) => let Report_Sheet = yourTab{[Item="Report",Kind="Sheet"]}[Data], #"Removed Top Rows" = Table.Skip(Report_Sheet,1), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column6", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column6.1", "Column6.2"}), #"Filled Down" = Table.FillDown(#"Split Column by Delimiter",{"Column6.2"}), #"Removed Top Rows1" = Table.Skip(#"Filled Down",4), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows1", [PromoteAllScalars=true]), #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{" 1/31/2021", "Report Effective Date"}}) in #"Renamed Columns" in yourFunction 

 

 

a problem could be that of making the rename of the new column "Report Effective Date" dynamic from the splitted date.
You should try to find a rule that always applies:
1) is it the last column of the table?
2) is it in a precise position of the table [12 position]?
3) 3) is it immediately after a column that has a well-defined name?
4) another rule ...

 


 

PhilipTreacy
Super User
Super User

Hi @Anonymous 

 

Extracting that date is not difficult, but how exactly do you intend to have a new column of dates?  What do you want your final data table to look like?

 

If I create a new column with that extracted data. you will also end up with lots of nulls/blanks in the rows where there is no date.  What do you wan to happen with these?

 

Please provide more info, sample data and an example of your desired result.

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors