Help formatting Data

[rs3782]

I have the following data:

Monday, October 16, 2023 – Friday, October 20, 2023
Ackerly, Lily
Class/Enrollments Pay rate: Yoga Instructor
Class Date	Class Time	Class name	# Clients	# Comps	Base Pay	Assistant Pay	Bonus Pay	Earnings
10/16/2023	4:30 pm	RECOVER		5	0	50	—	—	50
						50		—	50
			# Services	# Clients	# Comps	Base Earnings		Earnings
Total for Ackerly, Lily		1	5	0	$50.00			50

I'd like to get it in the following format:

Instructor	Pay Rate	Class Date	Class Time	Class name	# Clients	# Comps	Base Pay	Assistant Pay	Bonus Pay	Earnings
Ackerly, Lily	Yoga Instructor	10/16/2023	4:30 pm	RECOVER		5	0	50			50

Please help!

[BA_Pete]

Hi @rs3782 ,

Try this example query. It probably won't handle scenarios more advanced than what you've provided as your example data, but it should give you an idea of what's possible:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVHBTsMwDP0Vq+NY0bRjO+y2lSIhgYbGhISqHkwJU0SaTHGG1Nv+Ab5wX0K6ZoMiVMbBzrPzYj87eR7cavWMdQjz0uonbiAeh5CwZAi77TtcGdG5TFh7GYQB/GlFmJ/AOjCn5Ss30vW6EbI+/VkqkSjKlNFSVlxZgjuswaDlE3jUK4RrRdZsnH7zz6Jw6Yq4VBssRfUVKNwHA0ilaHq2WFfrBs2QeCPCwSmRIIvK+nim1YY8ztAooVZ0bBqzKB5HfrsXkyGDdeXQIkvnD9nC6xs5Y83ZuN324+hdom/fez70sQdwz82bKDn1T9bR/csYS21Rwotbd+dHPT3+NsPZiJ0z9lNkUBSf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    addInstructor =
    Table.AddColumn(
        Source,
        "Instructor",
        each if [Column1] = "Class Date" then "Instructor"
        else Source[Column1]{2}),
    addPayRate =
    Table.AddColumn(
        addInstructor,
        "Pay Rate",
        each if [Column1] = "Class Date" then "Pay Rate"
        else Text.AfterDelimiter(Source[Column1]{3}, ": ")),
    addEarnings =
    Table.AddColumn(
        addPayRate,
        "Earnings",
        each if [Column1] = "Class Date" then "Earnings"
        else Source[Column10]{5}),
    filterUnusedRows = Table.SelectRows(addEarnings, each ([Column2] <> " ")),
    removeUnusedCols = Table.SelectColumns(filterUnusedRows,{"Instructor", "Pay Rate", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Earnings"}),
    promoteHeaders = Table.PromoteHeaders(removeUnusedCols, [PromoteAllScalars=true])
in
    promoteHeaders

Example output:

Pete