Best way to query and output list from SharePoint list

[harleyvrodred]

I have a couple of SharePoint lists/tables I need to query and output a file or printout. I'm not sure if this can be done in Sharepoint or one of the Microsoft Apps. I'm just thinking exporting it to Excel and doing it there but they need to do this on a regular basis.

Aside from page output formatting there need to be a formula to determine the Status (Current/Expired). If they are a lifetime member, then they will always be current. Otherwise, they need to renew yearly to not be in an expired state.

Thoughts?
-Tim

Members:

Last Name	First Name	Member ID	Email	Lifetime Member
Smith	John	222	JohnSmith@me.com	No
Armold	Paul	456	PaulArnold@you.com	Yes
Murphy	Jack	9263	JackMurphy@xx.com
Lowe	Rebeka	37989	RebLowe@some.net	no

Bank:

Date	Amount	Member ID	Description
7/20/2022	10.00	222	Renewal
7/20/2022	15.00	456	New
7/20/2022	10.00	9263	Renewal
11/20/2020	15.00	37989	New
7/19/2021	15.00	222	New
4/2/2019	15.00	9263	New

What I'd like for an output would be something like:

Arnold, Paul Email: JohnSmith@me.com Status: Current
7/20/2022 $10.00 Renewal
4/2/2019 $15.00 New
Lowe, Rebeka Email: RebLowe@some.net Status: Expired
11/20/2020 $15.00 New
Murphy, Jack Email: JackMurphy@xx.com Status: Current
7/20/2022 $10.00 Renewal
4/2/2019 $15.00 New
Smith, John Email: JohnSmith@me.com Status: Expired
7/20/2022 $10.00 Renewal
7/19/2021 $15.00 New

 

[GraH]

I'm thinking Power Query here. If I'm not mistaken it can connect to a SP list and fetch the data.
Not sure what tables on SP look like. Are those webparts widgets on a page? It might work to use the website connection, but I never tried that.
If that works you can combine the data from both sources in PQ and you would have a refreshable solution in place.

 

[jdellasala]

You're right in that Power Query is the way to go.
tblMembers

Book1
	A	B	C	D	E
1	Last Name	First Name	Member ID	Email	Lifetime Member
2	Smith	John	222	JohnSmith@me.com	No
3	Armold	Paul	456	PaulArnold@you.com	Yes
4	Murphy	Jack	9263	JackMurphy@xx.com
5	Lowe	Rebeka	37989	RebLowe@some.net	no
Sheet1

tblMembers Query

let
    Source = Excel.CurrentWorkbook(){[Name="tblMembers"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Last Name", type text}, {"First Name", type text}, {"Member ID", Int64.Type}, {"Email", type text}, {"Lifetime Member", type text}}),
    MergedColumns = Table.CombineColumns(ChangedType,{"Last Name", "First Name"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Name"),
    ReorderedColumns = Table.ReorderColumns(MergedColumns,{"Name", "Email", "Member ID", "Lifetime Member"})
in
    ReorderedColumns

tblBank

Book1
	G	H	I	J
1	Date	Amount	Member ID	Description
2	07/20/2022	10	222	Renewal
3	07/20/2022	15	456	New
4	07/20/2022	10	9263	Renewal
5	11/20/2020	15	37989	New
6	07/19/2021	15	222	New
7	04/02/2019	15	9263	New
Sheet1

tblBank Query

let
    Source = Excel.CurrentWorkbook(){[Name="tblBank"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Int64.Type}, {"Member ID", Int64.Type}, {"Description", type text}}),
    SortedRowsByDate = Table.Sort(ChangedType,{{"Date", Order.Descending}}),
    RemovedOlderDuplicates = Table.Distinct(SortedRowsByDate, {"Member ID"})
in
    RemovedOlderDuplicates

MergedReport

Book1
	L	M	N	O	P	Q	R
1	Name	Email	Member ID	Lifetime Member	Date	Amount	Description
2	Smith, John	JohnSmith@me.com	222	No	07/20/2022	10	Renewal
3	Armold, Paul	PaulArnold@you.com	456	Yes	07/20/2022	15	New
4	Murphy, Jack	JackMurphy@xx.com	9263		07/20/2022	10	Renewal
5	Lowe, Rebeka	RebLowe@some.net	37989	no	11/20/2020	15	New
Sheet1

MergedReport Query

let
    Source = Table.NestedJoin(Members, {"Member ID"}, Bank, {"Member ID"}, "Bank", JoinKind.LeftOuter),
    ExpandedBank = Table.ExpandTableColumn(Source, "Bank", {"Date", "Amount", "Description"}, {"Date", "Amount", "Description"})
in
    ExpandedBank

The Merge is Merge as New. Doesn't have to be, it could be the next step in tblMembers Query.
When the Bank table is sorted by date Descending, and then Duplicates are removed, they're removed from the oldest (bottom of the column) up.
Current isn't included because the logic escaped me.
Arnold, Paul is Current with a 7/20/2022 $10.00 Renewal, but
Smith, John is Expired with the same 7/20/2022 $10.00 Renewal. I assume that's a typo.
Explain how someone is Current/Expired.
Please use XL2BB when posting data.

 

[GraH]

jdellasala, where does SharePoint features in your solution as that's what the thread is about?

 

[jdellasala]

jdellasala, where does SharePoint features in your solution as that's what the thread is about?

Oh what a dope I am! I completely forgot about that (obviously).
I haven't had access to SharePoint for over two years, and it's always changing, and different approaches depending on the situation.
Watch this video - it's one of the latest on the subject, presented by an Excellent presenter! Less than 15 minutes.