handling segment wise average from double heading with one date range

[Anonymous]

1-Oct-22	1-Oct-22	1-Oct-22	2-Oct-22	2-Oct-22	2-Oct-22	3-Oct-22
Fin	Non Fin	Total Txns	Fin	Non Fin	Total Txns	Fin
241	61	302	202	39	241	78
193	79	272	348	63	411	349
172	38	210	139	20	159	268
371	67	438	301	65	366	106
302	78	380	355	48	403	196

[v-rzhou-msft · Accepted solution]

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.