Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All ,
I know this error may have been raised previously but i couldn't find a solution that could fit the problem i am facing.
I have data that are connected to SAP BW and schedule to refresh daily but for some reason, It was working fine until April 1st started, the data refresh schedule failed. So I found out the reason for that. Basically it fails because it didn't pass one of the below steps.
What i did in that highlighted step is replaced all null with "0" then unpivot the values anything above that step works fine with no issue with the data getting refreshed.
However, that worked fine because this was done during March 2019 then when April 1st started, it failed and throwed the above error. Is there away to fix this so it doesn't fail every month?
Your urgent help would be really appreciated.
Thanks
Abdul
Solved! Go to Solution.
Hi @Abdul2020
You need to replace value in whole table.
You can do this by using the Table.ColumnNames function in replacement of the specific column list. E.G. Table.ReplaceValue(#"Last Step",null,"-",Replacer.ReplaceValue,Table.ColumnNames(#"Last Step"))
As tested, it can be refreshed succefully with this method.
This is my code.
let Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\4\4.30\4.30.xlsx"), null, true), Sheet5_Sheet = Source{[Item="Sheet5",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Sheet5_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type date}, {"Column4", type any}, {"Column5", type date}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"name", type text}, {"10/1/2018", Int64.Type}, {"11/1/2018", type date}, {"12/1/2018", Int64.Type}, {"1/1/2019", type date}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type1")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"name"}, "Attribute", "Value") in #"Unpivoted Other Columns"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Abdul2020
You need to replace value in whole table.
You can do this by using the Table.ColumnNames function in replacement of the specific column list. E.G. Table.ReplaceValue(#"Last Step",null,"-",Replacer.ReplaceValue,Table.ColumnNames(#"Last Step"))
As tested, it can be refreshed succefully with this method.
This is my code.
let Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\4\4.30\4.30.xlsx"), null, true), Sheet5_Sheet = Source{[Item="Sheet5",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Sheet5_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type date}, {"Column4", type any}, {"Column5", type date}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"name", type text}, {"10/1/2018", Int64.Type}, {"11/1/2018", type date}, {"12/1/2018", Int64.Type}, {"1/1/2019", type date}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Changed Type1")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"name"}, "Attribute", "Value") in #"Unpivoted Other Columns"
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes Step "Replaced Value1".
It works fine but it doesn't pick up any new month added to the table as part of the data refresh.
e.g April isn't showing in the data because for some reason i think it fails at this step.
The orginal data coming from BW for the date was unpivoted. So i had to pivot the date. once i do that all the null values for each months shows up then i appliy "Replace Value1" step to replace null with "0"
Thanks Nishantjain for your prompt repsonse.
That step where it shows "unpivot selected Col.." its not for the months its for the products type as per screenshot below those products imported from BW pivoted so i had to unpivot them and they never change they are always the same values.
But am not sure if the values there are linked to the months.. still worth a try to unpivot other col on the below as well.
I will give that a try on a sample data with the same steps but unpivot other col then add addtional month and will see if if that solves the probelm.
If you you think there is another way or perhaps a formula to fix this please hit me with it. Thanks
I tried with a sample test and added April data then when refreshed the data it did show April but the step where it says replace value from null to "0" did not apply on april. as per screen shot below.
Can't attach a pbix here for some reaosn , it need be via dropbox or other external tool
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |