Power BI - Extract Transpose Alternate Rows

Question

I have a table which contains 1 column. It looks like this:

Column1
https://www.google.com
2019-03-12T11:15:30-05:00
https://www.twitter.com
2019-03-06T11:10:05-06:00

What I am trying to do is take the second row and transpose it to a new column.

Column1                  Column2
https://www.google.com   2019-03-12T11:15:30-05:00
https://www.twitter.com  2019-03-06T11:10:05-06:00

What is the best way to do this? I don't care if it's in the query editor or in DAX, however in DAX the columns aren't sorted the same way they came in (and in this case, each date is associated with the link above it)

Alexis Olson Alexis Olson · Accepted Answer · 2019-03-15T00:40:23

In the query editor, write a custom column that looks at the first few characters to distinguish between URL and date. Then pivot on that column.

Edit: It looks like you'll need to index each pair to pivot properly, so add an index from 0 and integer divide by 2 to get 0,0,1,1,2,2,3,3,..., which groups each pair together nicely.

let
    Source = <Your Source>,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.Start([Column1],1) = "h" then "URL" else "Date"),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Custom]), "Custom", "Column1")
in
    #"Pivoted Column"

Power BI - Extract Transpose Alternate Rows

1 Answers