handling segment wise average from double heading with one date range

Hi Anonymous ,

 

I think you can try Matrix visual to achieve your goal. Your table should look like as below.

My Original table looks like as below.

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bU25EcAgDFslR50CbMB4gZRZgGOANGT/Lki0aXSS9bj3cD0znOF+57HZnx5nD5LTUhUgURaqL7BGM7lC4KK5Iaekvl1jHvfElkRA3V01DhsikbQgGOt2+cvQ1QKD88mXOz4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"1-Oct-22" = _t, #"1-Oct-22.1" = _t, #"2-Oct-22" = _t, #"2-Oct-22.1" = _t, #"3-Oct-22" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"1-Oct-22", type text}, {"1-Oct-22.1", type text}, {"2-Oct-22", type text}, {"2-Oct-22.1", type text}, {"3-Oct-22", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each Date.AddDays(#date(2022,10,01), Number.RoundUp([Index]/2)-1)),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Column1", "Index", "Date"}, "Attribute", "Value"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Column","",Replacer.ReplaceText,{"Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each [Attribute]-1),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Category"}, {"Custom", "Index"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Category", type text}, {"Date", type date}, {"Value", Int64.Type}, {"Index", Int64.Type}})
in
    #"Changed Type2"

Then the new table should look like as first screenshot. Finally, you need to create a matrix.

 

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.