Custom Column - day, month, year - easy one for you smart people

[Anonymous]

I'm sure there is an easy answer here but I haven't been able to find it.  I want all the days to be transformed to day/month/year.  The month and year are in the first cell column1 and the days are in rows 3-33 right below.  A photo is attached of the power query and a seperate photo of what I think the output might look like.  Thank you for the help as I learn more about the power of PQ!

  

[adudani]

hi @Anonymous ,

create a blank query and in the advanced editor copy paste the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc4xDoAgEETRu1CbsDOoaO8tCIXH8Paa/TZ/QzEvjFGi11B1WGUuo1z3k5eXsy27Zrfsnu3ZI3uyCg5rMRd7AQhBEMIQiFCM8v/IKEYxilGMYhSjGKWhtE+ZLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    max = Table.RowCount(Source)-2,
    StartDate = Date.FromText(Source{0}[Column1]),
    Custom1 = List.Dates(StartDate,max,#duration(1,0,0,0))
in
    Custom1

does this resolve the issue?

Here, the limitation is that, the dates generated are based on the numbers inputed (1.2...n) in this case 31. assuming it was 32, it would generate next month.

Let me know if this resolves the question

[Anonymous]

Thank you for taking the time to help and look at the problem at hand.  That seems a more complicated solution than I thought might be needed.

Would you help me with the purpose of this part of the code - Thank you!

Binary.FromText("Lc4xDoAgEETRu1CbsDOoaO8tCIXH8Paa/TZ/QzEvjFGi11B1WGUuo1z3k5eXsy27Zrfsnu3ZI3uyCg5rMRd7AQhBEMIQiFCM8v/IKEYxilGMYhSjGKWhtE+ZLw==", BinaryEncoding.Base64)

[adudani]

 When you use "enter data" in power bi, this creates an empty table and you are able to add data in rows and columns.

the whole source step is automatically created using those functions to read the rows and columns based on the data entered when you click on "Edit" or "Transform" after entering data.

Let me know if this resolves your question