User Profile
YalanWu_test
Helper I
Joined 4 years ago
User Widgets
Contributions
Re: Extracting Alphanumeric from Dynamic String Length and Format
Hi, drwillia ; You could create this funtion. = Table.AddColumn(#"Changed Type", "Custom", each List.Select(Text.SplitAny([Column1], " "), each Text.Length(_)=8)) The final show: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs/PSVFwzk8sLgGSpXnJmTkKugrGBqauRkDaw9TX0MDE0hLINDQwMDCyMLY0UorViVbycFEISs0rSQQpdjFTiPBRCA4FMoMdLQ0MjI1MwGpcqyoLSzMLFDwyi1IVzE1MFYzdzAwMTc0MlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(Text.SplitAny([Column1], " "), each Text.Length(_)=8)), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom") in #"Expanded Custom" Best Regards,Re: Refresh one table once a month, rest every day
Hi, Pikachu-Power ; As far as I know, this is not support. Scheduled flushes are set to flushes an entire dataset. The 10 tables belong to one data set, so different refresh plans cannot be set separately. https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data Best Regards,Re: AVERAGE FORMULA
Hi, Gheb_Gabriela ; You could create a measure. measure=if(hasonevalue([Cate]),[PAYOUT MEASURE],AVERGEX('TABLE',[PAYOUT MEASURE]) By HASONEVALUE, [Cate] should be the column in your matrix column field. If not right.Can you post sample data as text and expected output? Best Regards,Re: How can I remove leading zeroes from the data labels?
Hi, Anonymous ; You could create measure. Measure = IF(MAX('Table'[value])<1, MAX('Table'[value])) Measure2 = IF(MAX('Table'[value])>=1 ,MAX('Table'[value])) and change the measure format. Then create a feild parameter. The final show: Best Regards,Re: Grouping by values Y Axis
Hi, Anonymous ; I'm sorry I'm not familiar with Tableau. Could you please consdier sharing more details about it and posting expected result so it is clear on what needs to be implemented? And It would be great if there is a sample file without any sesentive information here. It makes it easier to give you a solution. Best Regards,Re: Rotate mi data using power query
Hi, rubenamn ; You also could create a custom column : = Table.Group(#"Filled Down", {"Machine", "Tech", "P/F", "QC", "Material"}, {{"Temp/Speed Wedge", each List.Max([#"Temp/Speed Wedge"]), type nullable text}, {"Temp/Speed Extruder", each List.Max([#"Temp/Speed Extruder"]), type nullable text}, {"Peel1", each Text.Combine( Table.Sort(_,{ {"Peel1", Order.Ascending}})[Peel1],"-") , type text}, {"Peel2", each Text.Combine( Table.Sort(_,{{"Peel2", Order.Ascending}})[Peel2],"-") , type text}, {"Peel3", each Text.Combine( Table.Sort(_,{{"Peel3", Order.Ascending}})[Peel3],"-") , type text}, {"Peel4", each Text.Combine( Table.Sort(_,{{"Peel4", Order.Ascending}})[Peel4],"-") , type text}, {"Peel5", each Text.Combine( Table.Sort(_,{{"Peel5", Order.Ascending}})[Peel5],"-") , type text}}) The final show: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lVJBisMwDPxK8bkQy7Fs59jd7SVsoZAeCqGHfUP/DyuPzMq0WWghmggpGY/GWld3CkTJ7d3hS6AkP2TvJdtJkOeKaFMYgRmIegpAqsjoMuqxCJ5/7nd5fX8ILMPibvvVSaoPyMME9MDYHZTaF/9HJXvSzaYbFESTUYcCVK3oRhw8osvoJv+g+zJctnSzURHbDMhf0F2qrHlpqhObaqWMHbFW4O3YO5xsDn7Rbb1KtQEXR/kNt6+BfP1r/lROMXuIxXcrMZnNbQRUONp6RFTG/DdOL/t43TD7/diwmM3iNr9ePXfr0alsinVV4FTMTxaL1tsv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machine = _t, Tech = _t, #"Temp/Speed Wedge" = _t, #"Temp/Speed Extruder" = _t, Peel1 = _t, Peel2 = _t, Peel3 = _t, Peel4 = _t, Peel5 = _t, Shear1 = _t, Shear2 = _t, Shear3 = _t, Shear4 = _t, Shear5 = _t, #"P/F" = _t, QC = _t, Material = _t]), #"Replaced Value" = Table.ReplaceValue(Source," ",null,Replacer.ReplaceValue,{"Machine", "Tech", "Temp/Speed Wedge", "Temp/Speed Extruder", "Peel1", "Peel2", "Peel3", "Peel4", "Peel5", "Shear1", "Shear2", "Shear3", "Shear4", "Shear5", "P/F", "QC", "Material"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","",null,Replacer.ReplaceValue,{"Machine", "Tech", "Temp/Speed Wedge", "Temp/Speed Extruder", "Peel1", "Peel2", "Peel3", "Peel4", "Peel5", "Shear1", "Shear2", "Shear3", "Shear4", "Shear5", "P/F", "QC", "Material"}), #"Removed Blank Rows" = Table.SelectRows(#"Replaced Value1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))), #"Filled Down" = Table.FillDown(#"Removed Blank Rows",{"Machine", "Tech", "P/F", "QC", "Material"}), #"Grouped Rows" = Table.Group(#"Filled Down", {"Machine", "Tech", "P/F", "QC", "Material"}, {{"Temp/Speed Wedge", each List.Max([#"Temp/Speed Wedge"]), type nullable text}, {"Temp/Speed Extruder", each List.Max([#"Temp/Speed Extruder"]), type nullable text}, {"Peel1", each Text.Combine( Table.Sort(_,{ {"Peel1", Order.Ascending}})[Peel1],"-") , type text}, {"Peel2", each Text.Combine( Table.Sort(_,{{"Peel2", Order.Ascending}})[Peel2],"-") , type text}, {"Peel3", each Text.Combine( Table.Sort(_,{{"Peel3", Order.Ascending}})[Peel3],"-") , type text}, {"Peel4", each Text.Combine( Table.Sort(_,{{"Peel4", Order.Ascending}})[Peel4],"-") , type text}, {"Peel5", each Text.Combine( Table.Sort(_,{{"Peel5", Order.Ascending}})[Peel5],"-") , type text}}) in #"Grouped Rows" Best Regards,Re: Visual throws query folding issue in report but works fine in corresponding Power BI dataset
Hi, Anonymous ; You can do some pretty complex things in the Power Query Editor and in DAX and the error message above is the error you get when Power BI admits defeat and says it can’t translate a DAX query generated by a visual on a report into a query against your data source. The cause is likely to be a combination of several of the of the following: A complex data model Complex DAX used in measures or calculated columns The use of dynamic M parameters Complex transformations created in the Power Query Editor How can you avoid it? Again, I can only offer general advice: Don’t do any transformations in the Power Query Editor if you’re using DirectQuery mode. If you want to use DirectQuery you should always make sure your data is modelled appropriately in whatever data source you’re using before you start designing your dataset in Power BI. Keep your data model as simple as possible. For example, avoiding bi-directional relationships is a good idea. Try to implement as much of the logic for your calculations in your data source and reduce the amount of DAX you need to write. Try to write your DAX in a different way in the hope that Power BI will be able to fold it. https://blog.crossjoin.co.uk/2022/05/08/understanding-the-we-couldnt-fold-the-expression-to-the-data-source-error-in-power-bi/ Best Regards,Re: Create a slicer which lets your day start at for example 6am.
Hi, Anonymous ; You could create a new table as slicer. slicer = VALUES(financials[Date]) Then create a measure. Measure = var _diff= DATEDIFF(MAX('slicer'[Date]),MAX('financials'[Date]),HOUR) return IF(_diff<=24&&_diff>=0,1,0) apply it into visual filter. This formula is only used when the visual object has a date field. If the visual object does not have a date, a new measure needs to be created as above. At present, this is the only method I can think of. Best Regards,
