Sum the latest data without blank values for each suppliers - Power BI

[Danoob]

Hello,

I'm looking for a way to filter values in a matrix table with the latest month with no blank rows
To give you an quick explanation I have :
- 1 dedicated date table with month, year, etc....
- 1 data table with different suppliers and the amount they delivered.

For example in the table below Supp 2 didn't deliver for month 3 so I would like to display month 2 total only for Section 1.
If anyone have a recommendation or the glance of an idea I would be very grateful

Thanks,

Month	1​	2​	3​
Section 1
Supp1	50​	20​	30​
Supp2	50​	60​
Total	100​	80​	30​
Section 2
Supp4	50​	60​	200​
Supp3	70​	80​	10​
Total	120​	140​	210​

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    tbl1 = Table.SelectRows(tbl, each ([Month] <> "Total")),
    tbl2 = Table.AddColumn(tbl1, "Section", each if Text.Start([Month],7) = "Section" then [Month] else null),
    tbl3 = Table.FillDown(tbl2,{"Section"}),
    tbl4 = Table.SelectRows(tbl3, each not Text.StartsWith([Month], "Section")),
    tbl5 = Table.Group(tbl4, {"Section"}, {"All", each _}),
    tbl6 = Table.TransformColumns(tbl5, {"All", each Table.AddColumn(_, "LastFullMonth", (x)=> let lpo = List.PositionOf(Record.ToList(x), null) in if lpo = -1 then 999 else lpo)}),
    tbl7 = Table.AddColumn(tbl6, "Month", each let min = List.Min([All][LastFullMonth]) in if min = 999 then Table.ColumnNames([All]){Table.ColumnCount([All]) - 3} else Table.ColumnNames([All]){min -1}),
    tbl8 = Table.AddColumn(tbl7, "Total", each List.Sum(List.Transform(Table.Column([All], [Month]), (x)=> Number.From(x)))),
    Result = Table.RemoveColumns(tbl8,{"All"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3	Column4		Section	Month	Total
2	Month	1	2	3		Section 1	2	80
3	Section 1					Section 2	3	210
4	Supp1	50	20	30
5	Supp2	50	60
6	Total	100	80	30
7	Section 2
8	Supp4	50	60	200
9	Supp3	70	80	10
10	Total	120	140	210
11
Sheet3

 

[Danoob]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    tbl = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    tbl1 = Table.SelectRows(tbl, each ([Month] <> "Total")),
    tbl2 = Table.AddColumn(tbl1, "Section", each if Text.Start([Month],7) = "Section" then [Month] else null),
    tbl3 = Table.FillDown(tbl2,{"Section"}),
    tbl4 = Table.SelectRows(tbl3, each not Text.StartsWith([Month], "Section")),
    tbl5 = Table.Group(tbl4, {"Section"}, {"All", each _}),
    tbl6 = Table.TransformColumns(tbl5, {"All", each Table.AddColumn(_, "LastFullMonth", (x)=> let lpo = List.PositionOf(Record.ToList(x), null) in if lpo = -1 then 999 else lpo)}),
    tbl7 = Table.AddColumn(tbl6, "Month", each let min = List.Min([All][LastFullMonth]) in if min = 999 then Table.ColumnNames([All]){Table.ColumnCount([All]) - 3} else Table.ColumnNames([All]){min -1}),
    tbl8 = Table.AddColumn(tbl7, "Total", each List.Sum(List.Transform(Table.Column([All], [Month]), (x)=> Number.From(x)))),
    Result = Table.RemoveColumns(tbl8,{"All"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3	Column4		Section	Month	Total
2	Month	1	2	3		Section 1	2	80
3	Section 1					Section 2	3	210
4	Supp1	50	20	30
5	Supp2	50	60
6	Total	100	80	30
7	Section 2
8	Supp4	50	60	200
9	Supp3	70	80	10
10	Total	120	140	210
11
Sheet3

Hello thanks a lot for you help. However I'm wondering if it is possible to do such but with DAX ? Beause my table on top is not directly presented like this in Power query. Looking forward to your answer. Many thanks