split column

[Syndicate_Admin]

Hi

Relatively new user.

I've uploaded a table I connected to on the web. I need to unpivot the table so I have a chronological list of the dates in the table.

I've tried splitting but run into an error problem as the information in row 1 is the year I need to link/associate with the dates listed. Also some of the days have extra ** I don't need.

Thanks

[spinfuzer · Accepted solution]

let
    Source = Web.Page(Web.Contents("https://www.nyse.com/markets/hours-calendars")),
    Data0 = Source{0}[Data],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Data0, {"Holiday"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","*","",Replacer.ReplaceText,{"Value"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Value", each Text.BeforeDelimiter(_, "("), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted Text Before Delimiter",{"Attribute", "Value"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}})
in
    #"Changed Type"

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

You can put the following code to advanced editor 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLBbsIwDEB/xeppq9AEZVA4bkNMY8BlSGhiHFJqkWiQICdl6m2/sd/blyy0dCppuFTVs/0cO1mtgjl+wTsy+v3+0TBiedAKZkqmLG/BhMmMUQ4dy5aYStQNvOAZuXTdWgUzRkZImGaGI8GrkFubQXfXGvQ8MGr7MoeFfsk0t06j5EfWbkexhkdBhqcX9jEmVBX5aOyl/aLBs1IpjEmUxvKnBXaqDYdoWEMPBxI76AwaqFvuAfeKBNs5g8+YnS92Qd8FvcIxySQaRGk4zJkRSlrdi0zxgPYjN3h212/IVpRT1+7nn1XnrMiph8dXL93lcB9elHpJF25cEahEIx0xvS36TFmiyFnGGx4M7hP7TiIv7XhpXPgWnMlPvRVH+xoa556r49k7CMMrkfhqpG8jpxZPnIQ2e6abex7h5pzcCx1PI1Qtygms138=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Holiday = _t, #"2024" = _t, #"2025" = _t, #"2026" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Holiday", type text}, {"2024", type text}, {"2025", type text}, {"2026", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Holiday"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","*","",Replacer.ReplaceText,{"Value"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Text.BeforeDelimiter([Value],"(")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Attribute", "Year"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column",{"Custom", "Attribute"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}, {"Year", Int64.Type}})
in
    #"Changed Type1"

Output

Best Regards!

Yolo Zhu

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

[dufoq3 · Accepted solution]

Same result, different approach:

let
    Source = Web.Page(Web.Contents("https://www.nyse.com/markets/hours-calendars")),
    Data0 = Source{0}[Data],
    YearsColNames = List.Buffer(List.RemoveNulls(List.Transform(Table.ColumnNames(Data0), each try Number.From(_) otherwise null))),
    StepBack = Data0,
    TransformToDates = Table.TransformColumns(StepBack,
     List.Transform(YearsColNames, (colName)=>
        { Text.From(colName), each Date.FromText(Text.From(colName) & ", " & Text.Trim(Text.BeforeDelimiter(Text.TrimEnd(_, {"*"}),"(")) , [Format="yyyy, dddd, MMMM %d", Culture = "en-US"]), type date })),
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(TransformToDates, {"Holiday"}, "Year", "Date")
in
    UnpivotedOtherColumns

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

You can put the following code to advanced editor 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZLBbsIwDEB/xeppq9AEZVA4bkNMY8BlSGhiHFJqkWiQICdl6m2/sd/blyy0dCppuFTVs/0cO1mtgjl+wTsy+v3+0TBiedAKZkqmLG/BhMmMUQ4dy5aYStQNvOAZuXTdWgUzRkZImGaGI8GrkFubQXfXGvQ8MGr7MoeFfsk0t06j5EfWbkexhkdBhqcX9jEmVBX5aOyl/aLBs1IpjEmUxvKnBXaqDYdoWEMPBxI76AwaqFvuAfeKBNs5g8+YnS92Qd8FvcIxySQaRGk4zJkRSlrdi0zxgPYjN3h212/IVpRT1+7nn1XnrMiph8dXL93lcB9elHpJF25cEahEIx0xvS36TFmiyFnGGx4M7hP7TiIv7XhpXPgWnMlPvRVH+xoa556r49k7CMMrkfhqpG8jpxZPnIQ2e6abex7h5pzcCx1PI1Qtygms138=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Holiday = _t, #"2024" = _t, #"2025" = _t, #"2026" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Holiday", type text}, {"2024", type text}, {"2025", type text}, {"2026", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Holiday"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","*","",Replacer.ReplaceText,{"Value"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each Text.BeforeDelimiter([Value],"(")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Attribute", "Year"),
    #"Merged Columns" = Table.CombineColumns(#"Duplicated Column",{"Custom", "Attribute"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Date", type date}, {"Year", Int64.Type}})
in
    #"Changed Type1"

Output

Best Regards!

Yolo Zhu

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

[spinfuzer · Accepted solution]

let
    Source = Web.Page(Web.Contents("https://www.nyse.com/markets/hours-calendars")),
    Data0 = Source{0}[Data],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Data0, {"Holiday"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","*","",Replacer.ReplaceText,{"Value"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Value", each Text.BeforeDelimiter(_, "("), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted Text Before Delimiter",{"Attribute", "Value"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Merged", type date}})
in
    #"Changed Type"