Selecting values to create a column

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.