Selecting values to create a column

[Anonymous]

I have a column with different informations and I want to separate that information in 3 new columns (Project ID, Project Name, Issue Date).

As you can see in the example, I have the Project ID in row 4 and the value of that ID in row 5, the Project Name in row 6 and the value of that name in row 7, the Issue Date in row 12 and the value of that Date in row 13. I want to create a column Project ID with the value "123456", a column Project Name with the value "blablabla" and Issue Date with the value "July 2020".

Any way to do this?

Thanks!!

[Anonymous · Accepted solution]

Hi @Anonymous ,

I have a test by your sample.

I think you can refer to this M code. This code is for situation of your example. In your example, there is only one row in three columns you need. So I use [Index]+1 in "Added Custom" and "Add Custom1" step, you can change the code to  [Index]+n if there are n rows in three columns you need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYy1AsI8vdydXUOUfD0c/MP8nUM8fT3A0uBiYCi/KzU5BIFTxeIciNjE1MzFBm/xNxUsEBSTiIEQaSzfBFSyWlgykjPxd851NfVD4dlnsXFpakKLoklEF1epTmVCkYGRgZgnkt+cmlual6JQlhqUXFmfh5YMMxIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cover Sheet" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cover Sheet", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "Custom", each if [Cover Sheet] = "Project ID" then 1 else if [Cover Sheet] = "Project Name" then 2 else if [Cover Sheet] = "Issue Date" then 3 else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Custom] <> null then [Index]+1 else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom.1", "Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Filter", each if [Index] <= [Custom.1] and [Index] >=[Custom.1]-1 then 1 else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom1", {{"Filter", null}}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Filter] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom.1", "Filter"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Custom"}, {{"Rows", each _, type table [Cover Sheet=nullable text, Custom=number]}}),
    #"Indexed" = Table.TransformColumns(#"Grouped Rows", {{"Rows", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
    #"Expanded Rows" = Table.ExpandTableColumn(Indexed, "Rows", {"Cover Sheet", "GroupIndex"}, {"Rows.Cover Sheet", "Rows.GroupIndex"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Rows", {{"Custom", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Rows", {{"Custom", type text}}, "en-US")[Custom]), "Custom", "Rows.Cover Sheet"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Rows.GroupIndex"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns1", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project ID", Int64.Type}, {"Project Name", type text}, {"Issue Date", type date}})
in
    #"Changed Type1"

After transform, result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a lot for your answer!!

It works... however, is it possible to apply this to a multiple excel files with the same structure? How would you do this?

Thanks,

Tiago Fernandes

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

[Anonymous]

Hello!!

For now I have this table:

Source.Name	Cover Sheet
bla iLessons Learned_v11.xlsx	null
bla iLessons Learned_v11.xlsx	1. PROJECT INFORMATION
bla iLessons Learned_v11.xlsx	null
bla iLessons Learned_v11.xlsx	Project ID
bla iLessons Learned_v11.xlsx	123554
bla iLessons Learned_v11.xlsx	Project Name
bla iLessons Learned_v11.xlsx	bla
bla iLessons Learned_v11.xlsx	PjM Name
bla iLessons Learned_v11.xlsx	bla bla
bla iLessons Learned_v11.xlsx	2. DOCUMENT INFORMATION
bla iLessons Learned_v11.xlsx	null
bla iLessons Learned_v11.xlsx	Issue Date
bla iLessons Learned_v11.xlsx	12.01.2021
bla iLessons Learned_v11.xlsx	Document Version
bla iLessons Learned_v11.xlsx	null
bla iLessons Learned_v11.xlsx	3. TEMPLATE INFORMATION
bla iLessons Learned_v11.xlsx	null
bla iLessons Learned_v11.xlsx	Template Version
bla iLessons Learned_v11.xlsx	1.
bla iLessons Learned_v11.xlsx	Template Owner
bla iLessons Learned_v11.xlsx	BrgP/MFE-PMO
bla iLessons Learned_v11.xlsx	null
bla iLessons Learned_v11.xlsx	null
bla iLessons Learned_v11.xlsx	Note:
bla iLessons Learned_v11.xlsx	- In case of a change on a role designation is needed according each plant, the entity is allowed to adapt the document to his reality.
bla iLessons Learned_v11.xlsx	null
bla iLessons Learned_v11.xlsx	null
renault iLessons Learned_v1xlsx	null
renault iLessons Learned_v1xlsx	1. PROJECT INFORMATION
renault iLessons Learned_v1xlsx	null
renault iLessons Learned_v1xlsx	Project ID
renault iLessons Learned_v1xlsx	123456
renault iLessons Learned_v1xlsx	Project Name
renault iLessons Learned_v1xlsx	renault
renault iLessons Learned_v1xlsx	PjM Name
renault iLessons Learned_v1xlsx	tiago
renault iLessons Learned_v1xlsx	2. DOCUMENT INFORMATION
renault iLessons Learned_v1xlsx	null
renault iLessons Learned_v1xlsx	Issue Date
renault iLessons Learned_v1xlsx	12.10.2021
renault iLessons Learned_v1xlsx	Document Version
renault iLessons Learned_v1xlsx	null
renault iLessons Learned_v1xlsx	3. TEMPLATE INFORMATION
renault iLessons Learned_v1xlsx	null
renault iLessons Learned_v1xlsx	Template Version
renault iLessons Learned_v1xlsx	1.1
renault iLessons Learned_v1xlsx	Template Owner
renault iLessons Learned_v1xlsx	BrgP/MFE-PMO
renault iLessons Learned_v1xlsx	null
renault iLessons Learned_v1xlsx	null
renault iLessons Learned_v1xlsx	Note:
renault iLessons Learned_v1xlsx	- In case of a change on a role designation is needed according each plant, the entity is allowed to adapt the document to his reality.
renault iLessons Learned_v1xlsx	null
renault iLessons Learned_v1xlsx	null
iLessons Learned_peugeot.xlsx	null
iLessons Learned_peugeot.xlsx	1. PROJECT INFORMATION
iLessons Learned_peugeot.xlsx	null
iLessons Learned_peugeot.xlsx	Project ID
iLessons Learned_peugeot.xlsx	100200
iLessons Learned_peugeot.xlsx	Project Name
iLessons Learned_peugeot.xlsx	peugeot
iLessons Learned_peugeot.xlsx	PjM Name
iLessons Learned_peugeot.xlsx	Gabriela
iLessons Learned_peugeot.xlsx	2. DOCUMENT INFORMATION
iLessons Learned_peugeot.xlsx	null
iLessons Learned_peugeot.xlsx	Issue Date
iLessons Learned_peugeot.xlsx	15.06.2021
iLessons Learned_peugeot.xlsx	Document Version
iLessons Learned_peugeot.xlsx	V2
iLessons Learned_peugeot.xlsx	3. TEMPLATE INFORMATION
iLessons Learned_peugeot.xlsx	null
iLessons Learned_peugeot.xlsx	Template Version
iLessons Learned_peugeot.xlsx	1.
iLessons Learned_peugeot.xlsx	Template Owner
iLessons Learned_peugeot.xlsx	BrgP/MFE-PMO
iLessons Learned_peugeot.xlsx	null
iLessons Learned_peugeot.xlsx	null
iLessons Learned_peugeot.xlsx	Note:
iLessons Learned_peugeot.xlsx	- In case of a change on a role designation is needed according each plant, the entity is allowed to adapt the document to his reality.
iLessons Learned_peugeot.xlsx	null
iLessons Learned_peugeot.xlsx	null

And I want to achieve this:

ProjectID	ProjectName	Date
123554	bla	12.01.2021
123456	renault	12.10.2021
100200	peugeot	15.06.2021

My code looks like this for now:
let
Source = SharePoint.Files("https://bla.sharepoint.com/sites/msteams_7490956/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsm" or [Extension] = ".xlsx")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "iLessons Learned")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Cover Sheet", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}})
in
#"Changed Type"

Thanks!

[lbendlin]

Are these data points always in the same row?  You know that you can address a table row with {rownumber}, right? So the Project ID value would be [Cover Sheet]{4}   (rows start at zero).

[Syndicate_Admin]

No, I didn't know that. How can I use it in this case?