Thank you for the video, I found it is very helpful! May I know how to put date format into the M code? For example, one column is date, and we need to add a row including a new date.
Hey Judy, I think if you want use custom date use #date(2023,01,01) or #datetime(2023,01,01,12,00,00) Format is #date(year,month,day) for date #datetime(year, month, day, hour, minute, second) for DateTime Do not use “” quotation
Yes we can. Learning M code unlocks new ways we can manipulate and manage data in our report. Not only rows, you can even create your own custom table based on calculations of other multiple tables. I highly recommend people to learn M query.
Hi I just wonder what If I only want to add 1 value (for example the title) at the first column instead of gotta fill the whole row (given the table has many columns and I don't want to go put the rest column = 0)
Great tips. But I wonder, what if the table have a lot of rows, say a thousand or two thousand rows, how can we make the location (row index) more dynamic? Is there M code that automatically know what is the last row so we can add + 1 to make a new row?
Hey there, You can always use List.Count() m function to get the last index. You can use Table.RowCount() as well Ensure it is subtracted by 1 since index starts at 0. Pick a function based on how much time they consume.
? newstep1=Table.InsertRows(newstep, 0,{[Location=SUMX(FILTER(Location - beginner, Location - beginner[Name] = "East"), Location - beginner[Location]) ,address='1.sum', Name=null ]}) how to add total row for Name[East] column? i thought so but it doesn't work! help me
Hey there, That is a DAX code. Power Query uses m code. Instead of filter, it uses Table.SelectRows() and List.Sum() Something like List.Sum(Table.SelectRows(newstep, each [Name] = “East”))[ListName] ListName is the list you want to sum from the table newstep. Hope this helps.
Why would you want to manually code like this? Makes no sense. My suggestion, albeit more work but far more manageable would be add row in a csv file, import this data and append to the database table, that way the data is separate from the visuals and far easier to update and debug.
excellent, thank you for sharing
My pleasure! Thanks for watching
your skill made me subscribe ....
Appreciate your comment. Thanks a lot.
Thanks this is informative 😊😊
How do I Automatically add a calculated measure to a new row everyday along with date in separate column ( Date stamp)
Awesome tutorial
Thanks
Thank you for the video, I found it is very helpful! May I know how to put date format into the M code? For example, one column is date, and we need to add a row including a new date.
Hey Judy,
I think if you want use custom date use #date(2023,01,01) or #datetime(2023,01,01,12,00,00)
Format is #date(year,month,day) for date
#datetime(year, month, day, hour, minute, second) for DateTime
Do not use “” quotation
Thanks Rosh
Can we add calculated row in power bi using m or Dax ?..
Yes we can. Learning M code unlocks new ways we can manipulate and manage data in our report.
Not only rows, you can even create your own custom table based on calculations of other multiple tables. I highly recommend people to learn M query.
Hi I just wonder what If I only want to add 1 value (for example the title) at the first column instead of gotta fill the whole row (given the table has many columns and I don't want to go put the rest column = 0)
Great tips. But I wonder, what if the table have a lot of rows, say a thousand or two thousand rows, how can we make the location (row index) more dynamic? Is there M code that automatically know what is the last row so we can add + 1 to make a new row?
Hey there,
You can always use List.Count() m function to get the last index.
You can use Table.RowCount() as well
Ensure it is subtracted by 1 since index starts at 0.
Pick a function based on how much time they consume.
? newstep1=Table.InsertRows(newstep, 0,{[Location=SUMX(FILTER(Location - beginner, Location - beginner[Name] = "East"), Location - beginner[Location])
,address='1.sum', Name=null
]})
how to add total row for Name[East] column? i thought so but it doesn't work!
help me
Hey there,
That is a DAX code. Power Query uses m code.
Instead of filter, it uses Table.SelectRows() and List.Sum()
Something like
List.Sum(Table.SelectRows(newstep, each [Name] = “East”))[ListName]
ListName is the list you want to sum from the table newstep.
Hope this helps.
Why would you want to manually code like this? Makes no sense. My suggestion, albeit more work but far more manageable would be add row in a csv file, import this data and append to the database table, that way the data is separate from the visuals and far easier to update and debug.