User Profile
pbix1
Resolver II
Joined 5 years ago
User Widgets
Contributions
Re: After transformed data, inside Table View the col go to the end
You can also try: Untick 'Enable Load' in Power Query for the relevant query Apply changes Go back to Powerquery Tick 'Enable Load' in Power Query for the relevant query Apply changes That should then reload the entire query to Table View as if it was a new query being added. In table view, you can also remove the DAX that refers to the query, replace it with something else as a holder and then put the original DAX back in. That should refresh it, but calculated columns will also move to the beginning. These can however be re-ordered if needed by deleting and re-creating in the positions you want. These are just things you could experiment with. Re-ordering calculated columns is a bit of a pain though..1.4KViews0likes0CommentsRe: Parsing list values in a nested table
Thanks ralf_anton and ronrsnfld for your suggestions. I eventually managed to get what I needed. It may be that I didn't explain well enough what I was trying to achieve, but the revised code below creates a 'Reference_Date' column within the Transform File(s) that can be switched and manipulated using Process_Date and SysTimestamp. Here is the code, and I hope it helps someone else trying to achieve something similar. Thanks. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5yNjBU0lEyMjAyMzAxMoEydQ1MdI1MQgyNrAwMgChKKVYHQ60xCWqNcKqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE_CODE = _t, PROCESS_DATE = _t, SYSTIMESTAMP = _t]), Change_Type_01 = Table.TransformColumnTypes(Source,{{"FILE_CODE", type text}, {"PROCESS_DATE", type text}, {"SYSTIMESTAMP", type text}}), Add_Transform_File = Table.AddColumn(Change_Type_01, "Transform File", each Table.TransformColumnTypes( Table.FromRecords({ [Process_Date = "20260424", SysTimestamp = "2026-04-24T12:00:00Z"], [Process_Date = "20260423", SysTimestamp = "2026-04-24T12:00:00Z"], [Process_Date = "20260422", SysTimestamp = "2026-04-24T12:00:00Z"] }),{"Process_Date", type text, "SysTimestamp", type text} ) ), Add_Reference_Date = Table.TransformColumns(Add_Transform_File, {"Transform File",each Table.FromColumns(Table.ToColumns(_)&{List.Transform([SysTimestamp],each Text.Middle(_,0,4)&Text.Middle(_,5,2)&Text.Middle(_,8,2))},Table.ColumnNames(_)&{"Reference_Date"})} ) in Add_Reference_Date569Views0likes0CommentsRe: Parsing list values in a nested table
Thanks both for the replies. That's not what I'm after I'm afraid though. What I'm trying to do is do this within the pre-expanded 'Transform File'. The code I posted is just sample code to replicate the issue. In the actual scenario I need to add a Reference_Date column to unexpanded tables held in a Transform File column. The columns Add_Reference_Date_proc and Add_Reference_Date_sys only show what I would like the Reference_Date column to do instead of the List.ReplaceValue part in the Add_SysDate step. The Add_SysDate step should really be called Add_Reference_Date to match the name of the column it is adding. The trouble I'm having is that while List.ReplaceValue works for what it does within that line of code, I would like to replace that part with Text.Middle([SysTimestamp,X,X)&Text.Middle([SysTimestamp,X,X) etc. so I can pick out the characters I want into being the Reference_Date. This itself doesn't work within that set of code in the step though, at least I can't get it to. I think it may need a further set of iteration but I can't work out how to build that into it. I hope that makes it clearer and really appreciate any help. Thanks.. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5yNjBU0lEyMjAyMzAxMoEydQ1MdI1MQgyNrAwMgChKKVYHQ60xCWqNcKqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE_CODE = _t, PROCESS_DATE = _t, SYSTIMESTAMP = _t]), Change_Type_01 = Table.TransformColumnTypes(Source,{{"FILE_CODE", type text}, {"PROCESS_DATE", type text}, {"SYSTIMESTAMP", type text}}), Add_Transform_File = Table.AddColumn(Change_Type_01, "Transform File", each Table.TransformColumnTypes( Table.FromRecords({ [Process_Date = "20260424", SysTimestamp = "2026-04-24T12:00:00Z"], [Process_Date = "20260423", SysTimestamp = "2026-04-24T12:00:00Z"], [Process_Date = "20260422", SysTimestamp = "2026-04-24T12:00:00Z"] }),{"Process_Date", type text, "SysTimestamp", type text} ) ), Add_SysDate = Table.TransformColumns(Add_Transform_File, {"Transform File",each Table.FromColumns(Table.ToColumns(_)&{List.ReplaceValue([SysTimestamp],"-","",Replacer.ReplaceText)},Table.ColumnNames(_)&{"Reference_Date"})} ), Add_REFERENCE_DATE_proc = Table.AddColumn(Add_SysDate, "REFERENCE_DATE_proc", each Text.Middle([PROCESS_DATE],0,8)), Add_REFERENCE_DATE_sys = Table.AddColumn(Add_REFERENCE_DATE_proc, "REFERENCE_DATE_sys", each Text.Middle([SYSTIMESTAMP],0,4)&Text.Middle([SYSTIMESTAMP],5,2)&Text.Middle([SYSTIMESTAMP],8,2)), Change_Type_02 = Table.TransformColumnTypes(Add_REFERENCE_DATE_sys,{{"REFERENCE_DATE_proc", type text}, {"REFERENCE_DATE_sys", type text}}) in Change_Type_02681Views0likes2CommentsParsing list values in a nested table
Hi The title of this post is essentially what I am trying to achieve, but the issue is fairly involved so I'll provide a bit of background. I have a dataflow that comes to a step which has a table containing number of a columns. One of these is a table column that holds a series of transform files. The step that follows this filters the tables in that column at row level by a value in one of the other columns which are non table columns, (so it's filtering the pre-expanded tables in the table column by a value in one of the other columns of the host table). These filter values vary across each row of the host table. The above works, but there are different sets of source files to process that are in different formats that I need to apply this logic to. The issue is - Some of these source/transform files contain the field with the date that the filter needs to be applied to in yyyyMMdd format. This format directly corresponds to the filter that I am applying from the parent level table, with which the above works OK. However, some of the sources contain the field with the date that needs filtering in the format yyyy-MM-ddThh:mm:ssZ. I've figured out how I can add a column into these tables so that if these contain whatever date needs filtering it can be referred to with the existing logic outside of these tables. The idea there is to add a column that derives yyyyMMdd from yyyy-MM-ddThh:mm:ssZ. I've got most of the way there using List.ReplaceValues to get yyyyMMddThh:mm:ssZ, but don't know how to get rid of the Thh:mm:ssZ part. What I really want to do is make the new column flexible, so I can adjust it to either: Text.Middle([SYSTIMESTAMP],0,4)&Text.Middle([SYSTIMESTAMP],5,2)&Text.Middle([SYSTIMESTAMP],8,2) or Text.Middle([PROCESS_DATE],0,8) I can then refer to this single column inside the transform tables, and filter against that by just adjusting the above according to the file format. The bottom of this post contains the M code which demonstrates what I want to do with this. In the 'Transform File', I would like the column 'Reference_Date' to be derivable from either 'Process_Date' or 'SysTimestamp'. The columns 'REFERENCE_DATE_proc' and 'REFERENCE_DATE_sys' show what I would like to incorporate into the generated 'Reference_Date' column inside the 'Transform File'. So to replace the part below with the Text.Middle() code above: List.ReplaceValue([SysTimestamp],"-","",Replacer.ReplaceText) If anyone can help with this I would really appreciate it. I'm thinking it would require yet a further iteration, but I can't work out how to build this into it. Many thanks. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg5yNjBU0lEyMjAyMzAxMoEydQ1MdI1MQgyNrAwMgChKKVYHQ60xCWqNcKqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FILE_CODE = _t, PROCESS_DATE = _t, SYSTIMESTAMP = _t]), Change_Type_01 = Table.TransformColumnTypes(Source,{{"FILE_CODE", type text}, {"PROCESS_DATE", type text}, {"SYSTIMESTAMP", type text}}), Add_Transform_File = Table.AddColumn(Change_Type_01, "Transform File", each Table.TransformColumnTypes( Table.FromRecords({ [Process_Date = "20260424", SysTimestamp = "2026-04-24T12:00:00Z"], [Process_Date = "20260423", SysTimestamp = "2026-04-24T12:00:00Z"], [Process_Date = "20260422", SysTimestamp = "2026-04-24T12:00:00Z"] }),{"Process_Date", type text, "SysTimestamp", type text} ) ), Add_SysDate = Table.TransformColumns(Add_Transform_File, {"Transform File",each Table.FromColumns(Table.ToColumns(_)&{List.ReplaceValue([SysTimestamp],"-","",Replacer.ReplaceText)},Table.ColumnNames(_)&{"Reference_Date"})} ), Add_REFERENCE_DATE_proc = Table.AddColumn(Add_SysDate, "REFERENCE_DATE_proc", each Text.Middle([PROCESS_DATE],0,8)), Add_REFERENCE_DATE_sys = Table.AddColumn(Add_REFERENCE_DATE_proc, "REFERENCE_DATE_sys", each Text.Middle([SYSTIMESTAMP],0,4)&Text.Middle([SYSTIMESTAMP],5,2)&Text.Middle([SYSTIMESTAMP],8,2)), Change_Type_02 = Table.TransformColumnTypes(Add_REFERENCE_DATE_sys,{{"REFERENCE_DATE_proc", type text}, {"REFERENCE_DATE_sys", type text}}) in Change_Type_02Solved809Views0likes5CommentsRe: Temporary table with filters, measure in matrix
Alexx22 I think Ritaf1983 is saying that you might need to look at how the whole thing is designed, as there might be a different way of doing it that works as you need. You could have the technical problems exactly because of the model. It's also difficult to say without seeing the pbix, but I would say it's also worth considering row versus filter context, as it sounds like this would have a bearing on it.678Views2likes0CommentsRe: Report with huge data not loading data in Power BI Desktop and PBI service
natyp I think the million row limit is a limitation of Direct Query, but it does depend on how things are set up. You might need to look at the construction of what you've put together and maybe try to break it down. I think the idea with Direct Query is to use query folding if possible, or have the data already shaped and summarized if you can. I've come across this limit before as well and had to think of a different design. You could try adding, (or starting with), a dummy table that is local to the report. If you then add direct queries, you should have the option for Mixed Mode. This combines Import and Direct Mode and may enable you to workaround the limitation. There are also settings related to memory in the Options pane that you could try adjusting. I know this isn't a specific solution but I hope it helps.880Views1like0CommentsRe: Crossjoin and then summarize
Hi Kazu I have done something similar to this, but with the summarisations done before the calculations. Obviously replace the tables, calculations etc. with your own. However, most of it will work using the generic naming. Hopefully this may help you. The DAX below is to create a calculated table. PS - if anyone reading this has any ideas on how to improve this or make it more efficient, I would be interested to know. Thanks. Table = VAR SP01 = SELECTCOLUMNS('Table_A',"Week_Code_A",'Table_A'[Week_Code_A],"Data_A",'Table_A'[Data_Value_A]) VAR SP02 = SELECTCOLUMNS(FILTER('Table_B','Table_B'[Flag_B]="B"),"Week_Code_B", 'Table_B'[Week_Code_B],"Data_B",CONVERT('Table_B'[Data_Value_B],DOUBLE)) VAR SP03 = SELECTCOLUMNS(FILTER('Table_C','Table_C'[Flag_C]="C"),"Week_Code_C", 'Table_C'[Week_Code_C],"Data_C",CONVERT('Table_C'[Data_Value_C],DOUBLE)) VAR SP04 = GROUPBY(SP01,[Week_Code_A],"Total_A",SUMX(CURRENTGROUP(),[Data_A])) VAR SP05 = GROUPBY(SP02,[Week_Code_B],"Total_B",SUMX(CURRENTGROUP(),[Data_B])) VAR SP06 = GROUPBY(SP03,[Week_Code_C],"Total_C",SUMX(CURRENTGROUP(),[Data_C])) VAR SP07 = CROSSJOIN(SP04,SP05,SP06) VAR SP08 = FILTER(SP07,AND([Week_Code_A]=[Week_Code_B],[Week_Code_B]=[Week_Code_C])) VAR SP09 = ADDCOLUMNS(SP08,"Calculation",IF([Total_A]=0,0,DIVIDE([Total_A]-[Total_B]-[Total_C],[Total_A]))) VAR SP10 = SELECTCOLUMNS(SP09,"Week_Code",[Week_Code_A],"Result_A",[Total_A],"Result_B",[Total_B],"Result_C",[Total_C],"Percentage",FIXED([Calculation],10)) RETURN CALCULATETABLE(SP10)1.1KViews0likes0CommentsRe: Premium vs Pro Workspaces - And Apps For Viewing By Free Users - Insight Appreciated
Hi Collinq Thanks for your reply. It would be great if we could but unfortunately we can't do that. The option is greyed out. I understand what you're saying, but this capacity is purchased by corporate IT. I am merely in one of numerous departments that can request for workspaces to be given 'the diamond'. There is then an internal cost payable for this to be doled out to workspaces by IT etc. It is also definitely Capacity and not Per User. So, I'm not sure exactly how this is working at that level, and it's not something I will have any influence over, just that if we already have a licence then it shouldn't cost any more to re-assign it.12KViews0likes0Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.