Can I get only 'Date' values and removing other type value until column header with M code?

[jeongkim]

Hi,

Can I get only 'Date' values with M code? 

I wanna make 'Contract signed date' as a header but data source has unnecessary values on top and it is dynamic. 

If it was just null then it would be easy but any code for this? 

I'm currently using remove top 2 rows and promoted headers but just in case column header order in row change to row3 or row4 whatsoever.

[Ashish_Mathur]

Hi,

This line of M code should work

=Table.Skip(name of previous step, each not List.Contains(Record.FromList(_), "Contract signed date"))

Hope this helps.

[jeongkim]

Hi,

Can you help me amend my code?

Cuz List code shows automatic columns which is not for final output. 

// Get list of files in SharePoint folder
Documents = Source{[Name="Documents"]}[Content],
// Get list of files in SharePoint folder
#"XXX" = Documents{[Name="XXX"]}[Content],
// Get list of files in SharePoint folder
#"5G SvC_master - GR" = #"XXX"{[Name="5G SvC_master - GR"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(#"5G SvC_master - GR",{"Content", "Name", "Date created"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"Name", Order.Descending}}),
#"Kept First Rows1" = Table.FirstN(#"Sorted Rows",1),
#"Added Custom" = Table.AddColumn(#"Kept First Rows1", "Custom", each Excel.Workbook([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Custom.Name", "Custom.Data", "Custom.Item", "Custom.Kind", "Custom.Hidden"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Hidden] = false) and ([Custom.Kind] = "Sheet")),
#"Kept First Rows" = Table.FirstN(#"Filtered Rows",1),
#"Removed Other Columns1" = Table.SelectColumns(#"Kept First Rows",{"Name", "Custom.Name", "Custom.Data", "Date created"}),
// Dynamically get column names and expand
#"Listed Columns" = Table.ColumnNames(#"Removed Other Columns1"[Custom.Data]{0}),
#"Expanded Custom.Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom.Data", #"Listed Columns"),
#"Removed Top Rows" = Table.Skip(#"Expanded Custom.Data",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",
{{Table.ColumnNames(#"Promoted Headers"){0}, "Source Name"},
{Table.ColumnNames(#"Promoted Headers"){1}, "Sheet Name"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type)
in
#"Added Index"

[Ashish_Mathur]

i obviously cannot understand anything by just looking at a screenshot and a piece of code.  Anyways, in the code that i shared with you, replace the text within double quotes with column.

[jeongkim]

pls see the steps

[jeongkim]

not sure how to apply your code into my steps. 

[Bibiano_Geraldo]

Hi @jeongkim ,

After removed the top 2 rows, instead of promote first row as header, change the data type to date, now you will se errors in your column, just choose remove errors, and manualy give a name for you column:

1-Remove top x rows

Now your data should look like this:

2- Change column type  to Date:

Now your Table should look like this, as you can see, there's error in row that contain text:

3- Remove errors:

Your table should look like this:

4- Rename your column as you need.

[jeongkim]

Hi, 

Removing error is good idea. 

But what I need is not using removing top 2 rows since it has risk when column header place change to row #1 or row #3.

also column order could change in the future so Im trying to avoid type column name column by column manually. 

[mh2587]

1. Click the Remove Rows button in PQ
2. Select Remove Top Rows from the dropdown menu
3. Enter the number of rows to remove in the Number of rows text field in your case enter 2
4. Click OK

[jeongkim]

pls read my query before you answer 🙂