Create a table using columns from another table

[KatiS]

Hi everyone, 

I am a Power BI novice and need help with creating a new table using columns from another table. The original table is very large, with a lot of other columns, but I summarised the columns that I need to bring it into a new table as per the image below.  There are multiple projects that have start and end dates for the different project phases. I would need to create a column with the phases associated with the various dates, and then display start and end dates as per those phases. So I need to combine the various date columns into just two: start and end dates and those dates to be associtated with the phase. Hope this makes sense. 

This seems rather complex to me, and woud appreciate some help in creeting this. 

[dufoq3]

Hi @KatiS, provide sample data as table so we can copy paste, and expected result based on sample data. Thank you.

[KatiS]

Name	No	Bid Start Date	Bid End Date	Secured Date	Project Start date	Project end date
Project A	1	2/03/2024	1/05/2024	31/05/2024	15/06/2024	15/06/2027
Project B	2	15/07/2024	13/10/2024	12/12/2024	1/02/2025	31/01/2031
Convert to
Name	No	Phase	Start Date	End Date
Project A	1	Bid	2/03/2024	1/05/2024
Project A	1	Secured	31/05/2024	31/05/2024
Project A	1	Project	15/06/2024	15/06/2027
Project B	2	Bid	15/07/2024	13/10/2024
Project B	2	Secured	12/12/2024	12/12/2024
Project B	2	Project	1/02/2025	31/01/2031

[dufoq3 · Accepted solution]

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlFwVNJRMgRiI30DY30jAyMTEF/fwBTGNkbmGJrqG5hhcMyVYnUQBjqBDINKm8PVGusbGsA5RvpAhLALzDaF2mUI5BgbKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, No = _t, #"Bid Start Date" = _t, #"Bid End Date" = _t, #"Secured Date" = _t, #"Project Start date" = _t, #"Project end date" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "No"}, "Phase", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Phase", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Phase", "Date Type"}),
    TransformedDateType = Table.TransformColumns(#"Split Column by Delimiter", {{"Date Type", each if Text.Lower(_) = "date" then "Start Date" else Text.Proper(_), type text}}),
    #"Pivoted Column" = Table.Pivot(TransformedDateType, List.Distinct(TransformedDateType[#"Date Type"]), "Date Type", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null, each [Start Date], Replacer.ReplaceValue, {"End Date"})
in
    #"Replaced Value"

[KatiS]

Thank you. It looks perfect! exactly what I am after. I am bit lost, however, as to what the code in the "Source =...."  means and what sections in the code do I need to adjust at my end to connect to my table. My original table is called "Data". How do I link what you have in your Source code to my "Data" table?

[dufoq3]

Hi, have you read note below my posts?

BTW. The source code of my query is automatic generated. I used Enter Data button in PQ user interface. If you enter data like this - it will generate similar code.

[KatiS]

Sorry I have completely missed that. I thought it was just part of the forum generic txt. Makes sense. Easy. Thank you. 

[dufoq3]

You're welcome.