Data Manipulation Help

[Derek0834]

In this screenshot I have Calender Year and Month and Resource Units. What Would be the best way to get this data into a date table of sorts. Each resource units is a different day 1-31 for days of month.

[dufoq3 · Accepted solution]

Hi @Derek0834,
My sample data:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVjLcsJADPuXPXNY0tDHH/TcK8MfML31/0tmYOO1Lck5LEySfciSbBOu17b0ZW2ndn6M75/HR3+O1Yxuvrubk83369D1PG6ngWZ5oclWZrsgVP5eZa/fv/v99WUwvXlMKPrsGp3NWIwxGDSr1yvbyT9DqLNrhrAn9Fw8ICYLApdJheSLaw2ad0YPsikTiZkJmsgA+kD0sPyq5hlz2z4Mms9WzC/lb7UOe9qg+bLc6Dj4vIqH/R5erHP3iJQiFbQVrvb7Fs4ozixoRAyDUIUdCIIFWgdWk5mVqynbt7qT8lMxR8XybF2KZuKmqhIrA4xjO982rlmzDdbUv6oVh1UZVRPivgbN6tGoTGO2ZUSj+Z6e0b+UWlnY6DBGTQzGoBn9S4nEnFlBhK49PaN/IUeoQ1lqMqpT90z9SxUy5V82Bwtu0Ez9SxW3iiWQ15EZvFh7/1Kss3hzk1btZeFMLxdVMthcJDmCGwgaNVpluiKukpc54U84W6bD/qWyFBmFmSmPyqAJvZ2dXqkvLJmCjdP+tcEa/YtlBZOEEaTIDZKt7cAvRGaiCvKMME9PeP9S6avCVzkX9zFopv7F8uiIi5Ho2TxPz/T+pTav6oe0ytcZNKN/oRCUUFWPY+QGzehfqnQwpBWBUYZ5seL715ETqrnP11g46Z9jKEHRMzZUEQgEhf5VKUFHpEPEGwPd/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calendar Year" = _t, CALENDAR_MONTH = _t, UNIT_OF_MEASURE = _t, RESOURCE_UNITS_1 = _t, RESOURCE_UNITS_2 = _t, RESOURCE_UNITS_3 = _t, RESOURCE_UNITS_4 = _t, RESOURCE_UNITS_5 = _t, RESOURCE_UNITS_6 = _t, RESOURCE_UNITS_7 = _t, RESOURCE_UNITS_8 = _t, RESOURCE_UNITS_9 = _t, RESOURCE_UNITS_10 = _t, RESOURCE_UNITS_11 = _t, RESOURCE_UNITS_12 = _t, RESOURCE_UNITS_13 = _t, RESOURCE_UNITS_14 = _t, RESOURCE_UNITS_15 = _t, RESOURCE_UNITS_16 = _t, RESOURCE_UNITS_17 = _t, RESOURCE_UNITS_18 = _t, RESOURCE_UNITS_19 = _t, RESOURCE_UNITS_20 = _t, RESOURCE_UNITS_21 = _t, RESOURCE_UNITS_22 = _t, RESOURCE_UNITS_23 = _t, RESOURCE_UNITS_24 = _t, RESOURCE_UNITS_25 = _t, RESOURCE_UNITS_26 = _t, RESOURCE_UNITS_27 = _t, RESOURCE_UNITS_28 = _t, RESOURCE_UNITS_29 = _t, RESOURCE_UNITS_30 = _t, RESOURCE_UNITS_31 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Calendar Year", "CALENDAR_MONTH", "UNIT_OF_MEASURE"}, "Day", "Resource Units"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Day", each Text.AfterDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Calendar Year", Int64.Type}, {"CALENDAR_MONTH", Int64.Type}, {"UNIT_OF_MEASURE", type text}, {"Day", Int64.Type}, {"Resource Units", Int64.Type}})
in
    #"Changed Type"

[speedramps · Accepted solution]

Hi Derek

Thanks for you well described question.

Next time please provide example data as text rather than a picture, so we can quicky import it rather than retype all the test data again.

Download the solution here

Click to get PBIX from OneDrive 

How it works ....

Select the year, month and uom columns and then unpivot the other columns

Add column > Extract >Text after delimiter, Advance options to get the day number after last _ in the the resource name

Change day data tyope from from text  to a number

Use the year, month and date to add a new date column

Remove the unneeded columns

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[dufoq3 · Accepted solution]

Hi @Derek0834,
My sample data:

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVjLcsJADPuXPXNY0tDHH/TcK8MfML31/0tmYOO1Lck5LEySfciSbBOu17b0ZW2ndn6M75/HR3+O1Yxuvrubk83369D1PG6ngWZ5oclWZrsgVP5eZa/fv/v99WUwvXlMKPrsGp3NWIwxGDSr1yvbyT9DqLNrhrAn9Fw8ICYLApdJheSLaw2ad0YPsikTiZkJmsgA+kD0sPyq5hlz2z4Mms9WzC/lb7UOe9qg+bLc6Dj4vIqH/R5erHP3iJQiFbQVrvb7Fs4ozixoRAyDUIUdCIIFWgdWk5mVqynbt7qT8lMxR8XybF2KZuKmqhIrA4xjO982rlmzDdbUv6oVh1UZVRPivgbN6tGoTGO2ZUSj+Z6e0b+UWlnY6DBGTQzGoBn9S4nEnFlBhK49PaN/IUeoQ1lqMqpT90z9SxUy5V82Bwtu0Ez9SxW3iiWQ15EZvFh7/1Kss3hzk1btZeFMLxdVMthcJDmCGwgaNVpluiKukpc54U84W6bD/qWyFBmFmSmPyqAJvZ2dXqkvLJmCjdP+tcEa/YtlBZOEEaTIDZKt7cAvRGaiCvKMME9PeP9S6avCVzkX9zFopv7F8uiIi5Ho2TxPz/T+pTav6oe0ytcZNKN/oRCUUFWPY+QGzehfqnQwpBWBUYZ5seL715ETqrnP11g46Z9jKEHRMzZUEQgEhf5VKUFHpEPEGwPd/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Calendar Year" = _t, CALENDAR_MONTH = _t, UNIT_OF_MEASURE = _t, RESOURCE_UNITS_1 = _t, RESOURCE_UNITS_2 = _t, RESOURCE_UNITS_3 = _t, RESOURCE_UNITS_4 = _t, RESOURCE_UNITS_5 = _t, RESOURCE_UNITS_6 = _t, RESOURCE_UNITS_7 = _t, RESOURCE_UNITS_8 = _t, RESOURCE_UNITS_9 = _t, RESOURCE_UNITS_10 = _t, RESOURCE_UNITS_11 = _t, RESOURCE_UNITS_12 = _t, RESOURCE_UNITS_13 = _t, RESOURCE_UNITS_14 = _t, RESOURCE_UNITS_15 = _t, RESOURCE_UNITS_16 = _t, RESOURCE_UNITS_17 = _t, RESOURCE_UNITS_18 = _t, RESOURCE_UNITS_19 = _t, RESOURCE_UNITS_20 = _t, RESOURCE_UNITS_21 = _t, RESOURCE_UNITS_22 = _t, RESOURCE_UNITS_23 = _t, RESOURCE_UNITS_24 = _t, RESOURCE_UNITS_25 = _t, RESOURCE_UNITS_26 = _t, RESOURCE_UNITS_27 = _t, RESOURCE_UNITS_28 = _t, RESOURCE_UNITS_29 = _t, RESOURCE_UNITS_30 = _t, RESOURCE_UNITS_31 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Calendar Year", "CALENDAR_MONTH", "UNIT_OF_MEASURE"}, "Day", "Resource Units"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Day", each Text.AfterDelimiter(_, "_", {0, RelativePosition.FromEnd}), type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Text After Delimiter",{{"Calendar Year", Int64.Type}, {"CALENDAR_MONTH", Int64.Type}, {"UNIT_OF_MEASURE", type text}, {"Day", Int64.Type}, {"Resource Units", Int64.Type}})
in
    #"Changed Type"