SOLVED

# Having trouble transposing a table

Frequent Contributor

# Having trouble transposing a table

I am trying to do the following transpose, everything I have try so far failed. This is a sample the original table is a lot bigger. I need it in power query, thanks.

 Week True False Count 4/12/2020 - 27 4/12/2020 4/19/2020 4/26/2020 5/3/2020 5/10/2020 5/17/2020 5/24/2020 5/31/2020 6/7/2020 4/12/2020 + 4 - 27 100 138 116 5 169 90 152 1 4/19/2020 + 1 + 4 1 10 115 2 2 2 249 4/19/2020 - 100 4/26/2020 + 10 4/26/2020 - 138 5/3/2020 - 116 5/10/2020 - 115 5/10/2020 + 5 5/17/2020 + 2 5/17/2020 - 169 5/24/2020 + 2 5/24/2020 - 90 5/31/2020 + 2 5/31/2020 - 152 6/7/2020 - 249 6/7/2020 + 1
best response confirmed by Ocasio27 (Frequent Contributor)
Solution

# Re: Having trouble transposing a table

It's not clear what shall be the logic of how to group data if one value from the pair is missed. For example, here is only minus for the week from May 03

In the sample you group it with the value from next week, again not clear why

If ignore that the grouping could be done as

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{
{"Week", type date},
{"True False", type text},
{"Count", Int64.Type}
}
),
ListOfWeeks =
List.Transform(
List.Distinct(#"Changed Type"[Week]),
each Text.From(_)
),
#"Grouped Rows" = Table.Group(
#"Changed Type",{"True False"},
{
{"Count",
each
Table.Transpose(
Table.RemoveColumns(_,{"True False"})
), [PromoteAllScalars=true]
)
}
}
),
#"Expanded Count" = Table.ExpandTableColumn(
#"Grouped Rows",
"Count",
ListOfWeeks, ListOfWeeks
)
in
#"Expanded Count"``````