powerquery - Automatic column deletion in Power BI query

Question

Welcome To Ask or Share your Answers For Others

powerquery - Automatic column deletion in Power BI query

posted Oct 7, 2021 in Technique[技术] by 深蓝 (71.8m points)

powerquery - Automatic column deletion in Power BI query

Every 4 hours SAP automatically generate report within the same Excel file. The report name called “Data”. The “Data” file contain 11 columns are Data1, Data2, Data3, Data4, Data5, Data6, Data7, Data8, Data9, Data10 and Log Column. From Data 1 to Data 10 headers are always same format expect the log column. The log column keep changing every 4 hours.

Error

The Power Bi file contain old data(yesterday data) but the actual Excel file contain latest data. When I try to refresh I am getting the following error message “DATA The column 'Log Winshuttle Studio Console 12.0 SAP System: Client: SAP User - AE1:900:PETER Mode - Table; Script Name - DATA-MLGN.Qsq Records extracted - 287068 Date and Time - 26/01/2021 18:46:01 Execution Time - 0:02:13 Run Reason - Scheduling DATA-MLGN.Qsq at 02:41 PM' of the table wasn't found.”

Log Column

The log column contain like this “Log Winshuttle Studio Console 12.0 SAP System: Client: SAP User - AE1:900:PETER Mode - Table; Script Name - DATA-MLGN.Qsq Records extracted - 287068 Date and Time - 26/01/2021 18:46:01 Execution Time - 0:02:13 Run Reason - Scheduling DATA-MLGN.Qsq at 02:41 PM”. The highlighted in red font keep changing every 4 hours and remaining text are same.

Old Log Column

“Log Winshuttle Studio Console 12.0 SAP System: Client: SAP User - AE1:900:PETER Mode - Table; Script Name - DATA-MLGN.Qsq Records extracted - 287068 Date and Time - 26/01/2021 18:46:01 Execution Time - 0:02:13 Run Reason - Scheduling DATA-MLGN.Qsq at 02:41 PM”

New Log Column

Log Winshuttle Studio Console 12.0 SAP System: Client: SAP User - AE1:900:PETER Mode - Table; Script Name - DATA-MLGN.Qsq Records extracted - 287068 Date and Time - 27/01/2021 08:16:01 Execution Time - 0:02:13 Run Reason - Scheduling DATA-MLGN.Qsq at 02:41 PM

Result

What I am trying to achieve here…… The Log column showing when was last data updated in “Data” file so I would like to get the same thing in my data (part of data).