Forum Discussion
Extracting a date from a cell to a convert to a column
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 ...
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.
This is my desired result,
I am hoping for a more elegant solution that can save me sometime since I need to replicate it for multiple files.
regards,
Shweta
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!
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
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 ...