Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a new table depending on range

Hi there,

I want to create a new table depending on range, I got the following data;

Range.PNG

but I want to see it the following way;

range2.PNG

Thanks in advanced.

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

This M code will work

={Number.From([StartDate])..Number.From([EndDate])}

Then expand this new column.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

The solution send me the following error

Range3.PNG

You are missing the curly brackets at the beginning and end.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Sorry, now I got the following error

range4.PNG

Hi,

Share the link from where i can download your PBI file.  Also, share the back up MS Excel file from where you loaded data into the PowerBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi,

There are many files there.  Which one do i have to download?  Also, please ensure that all information in your files is in English.  I do not understand Spanish.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Re-send you the link
https://gcgcia-my.sharepoint.com/:f:/g/personal/vgarcia_d4b_mx/Emx1ZrC0U2hBnkLu7_XJw0cByx9KiC0YmdUjS...
There are only 2 files, a pbix and excel, both are in english.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
mahoneypat
Employee
Employee

To do this, you can create a new columns with a List of the dates between start and end date, and then expand that list to new rows.  See example M code below (note I started in a different date locale, so you may need to add a step with my example data to change to your locale).  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0gEyjeFsIBGrE61khJA10jeygLFNgLKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, Rate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"Rate", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Dates([StartDate], Duration.TotalDays([EndDate]-[StartDate])+1, #duration(1,0,0,0))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Dates", "Rate"}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Removed Other Columns", "Dates")
in
    #"Expanded Dates"

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.