Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to move the content of cell B1 to all cells in a new column and delete some other columns?

Hi all,

 

as a novice I guess my question is pretty basic:

We export every first day of a month one Excel table to a folder. Power BI imports all tables from that folder = 12 tables/year.

All tables have one thing in common: Cell B1 contains the name of the respective month.

 

1. That name of the month must be moved to a new column and it must be filled into every cell of this column up to the last used row.

Column A contains the header "name" and names of workers. Their number varies every month. I get the amount of cells to be filled with the name of the month using that formula: NumberOfRows = COUNTROWS(ALL(Table1[name]))

 

2. The second task is to delete a few columns.

In every table row number 3 contains numbers and combination of letters. Whenever cell 3 of a column contains either a number between 1 and 31 or any the expressions "NZ" or "SZ" or "BZ", then that column must be deleted.

 

I've no idea to proceed in both cases, any advice is very welcome.

 

 

1 ACCEPTED SOLUTION

No @Anonymous . That is not the way Powerquery works.

 

Let me explain. In a excel file, consider Name of student as first column, 2022 marks as second column, 2021 marks as thrid column. Add some dummy data and save this file as 1 in a folder. Create one more file with same columns but different data and place in same folder as file name 2.

 

Create a new PowerBI session. Get data from a folder and select the folder with excel files. After loading, in the query panel on left hand side, you will see that a folder (named helper query) with sample file, parameter, function and transform table query is created. Please look inside transform sample file query and you will see a table from one of the Excel file only. This query basically runs on each and every file. Any steps in this query will be repeated for each file. For example, rename first column as Student name (rather than name of student) in this query. For each file, the first column will be renamed and loaded.

 

Now under other queries folder in queries panel (on left hand side), you will see a single query. This is basically a combined table generated after transforming each file. 

 

Now we want to select columns dynamically. Let's assume you have placed excel files with 2022,2021 and 2020 marks as column. Note that 2020 is a additional column. If you refresh the queries, it will not consider 2020 column as when you did for the first time you had imported only 2021 and 2022. But if you had imported with 2020,2021 and 2022 for the first time, PQ will consider it everytime. The challenge is some times 2020 may be missing (let's assume). So PQ will throw error as column is missing. To avoid this delete promote headers step. This way PQ will not reference 2020 or 2021 etc. It will only reference column1,column2 etc. You can select columns that you need and delete others so that PQ will not throw error. This is what I had done in the queries that I shared.

 

I hope it helps.

 

Thanks.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Many thanks for your help. I get the expected result by replacing all dots with a comma and by adding the column names UF, KK, SU, UB and KA to the line #"Grouped Rows"...

 

Now I'm stuck applying the script when I change the source to:

 

Source = Folder.Files("C:\ALL TABLES FROM JANUARY TO DECEMBER\")

 

I created a new report, selected the above named folder and clicked on "Combine and load".

How do I proceed with your script?

 

I don't have enough knowledge to understand how to apply your advice: "you have to do this for excel file which has 31 days for the first time or you can import additional blank columns while importing. later it will work for any number days."

 

The problem is that all files in the folder vary concerning the number of days, the hours of absence, the month name, number of workers and name of workers. Attached ist the original file for another month and how I need it to be tramsformed. I updated the xls, it includes now January and February and can be downloaded downloaded here. 

Hi @Anonymous 

 

If you are importing files from a folder, you will have to use the logic inside the "transform sample file" table. This is where you will be transforming each excel file and then loading it.

 

What I mean by taking additional columns is that your last column is AK considering 23 days. When you are importing excel file in Powerquery, you need to import AK+8 columns. This is to account for 31 days. The logic will automatically select columns based on position of "Ist" text. I hope it helps.

 

The main query which appends each individual file will stay same.

 

Thanks

Anonymous
Not applicable

Hi Thingsclump

 

Does ist mean I need to unify all excel tables before importing them into the folder? So every table has to have 31 days? Unfortunately I can't change any of the incoming tables. This would require some kind of processing which is not possible. 

 

Or do I misunderstand you? How would I "import AK+8 columns"? Any table in the folder may include only 1 or up to 31 days; it can happen that some tables have only 5 days but most of them will have either 30 or 31 days.

 

Thank you for your help!

No @Anonymous . That is not the way Powerquery works.

 

Let me explain. In a excel file, consider Name of student as first column, 2022 marks as second column, 2021 marks as thrid column. Add some dummy data and save this file as 1 in a folder. Create one more file with same columns but different data and place in same folder as file name 2.

 

Create a new PowerBI session. Get data from a folder and select the folder with excel files. After loading, in the query panel on left hand side, you will see that a folder (named helper query) with sample file, parameter, function and transform table query is created. Please look inside transform sample file query and you will see a table from one of the Excel file only. This query basically runs on each and every file. Any steps in this query will be repeated for each file. For example, rename first column as Student name (rather than name of student) in this query. For each file, the first column will be renamed and loaded.

 

Now under other queries folder in queries panel (on left hand side), you will see a single query. This is basically a combined table generated after transforming each file. 

 

Now we want to select columns dynamically. Let's assume you have placed excel files with 2022,2021 and 2020 marks as column. Note that 2020 is a additional column. If you refresh the queries, it will not consider 2020 column as when you did for the first time you had imported only 2021 and 2022. But if you had imported with 2020,2021 and 2022 for the first time, PQ will consider it everytime. The challenge is some times 2020 may be missing (let's assume). So PQ will throw error as column is missing. To avoid this delete promote headers step. This way PQ will not reference 2020 or 2021 etc. It will only reference column1,column2 etc. You can select columns that you need and delete others so that PQ will not throw error. This is what I had done in the queries that I shared.

 

I hope it helps.

 

Thanks.

Anonymous
Not applicable

Thank you very much, that got me an idea how ist works.

 

Unfortunately it turned out the we need the numbers from column A, rows 5, 8, 11... in a custom column. I solved that part. But they should move up one row, so they appear in the rows 4, 7, 10... in the new column.

 

How could I achieve that?

 

Many thanks for some hints.

Thingsclump
Resolver V
Resolver V

Hi @Anonymous 

 

Is it possible to share sample input data please? You can remove sensitive information before sharing. Also share output table that you are expecting. This will be helpful to provide correct solution.

 

Thanks

 

Anonymous
Not applicable

Hi Thingsclump

 

You can download the attached file Mitarbeitermonatsuebersicht. It contains 2 sheets: 

OriginalOriginalexpected resultexpected result

 

The first picture shows a typical table, the second picture shows the expected result.

 

1. The number of workers in column A varies from month to month.

2. The number of days in row 3 varies from month to month between1 and 31.

3. It can happen that some of the columns U/K/FT/UF/KK/SU/UB/KA don't appear as they won't contain any number. 

4. It can happen that some of the columns NZ/SZ/BZ don't appear as they won't contain any number. Luckily I don't need them.

 

As you can see the numbers in the light red columns AB-AH are located one row lower than I would like to have them. All of them must appear in the rows of the names, but not in the rows NR.IW... After they have been moved, all rows NR.IW... and SVPfl must be deleted.

 

All light blue colums that contain a number in row 3 must be deleted.

 

The value of cell B1 must move into a new column which should be named "Monat". That value must be copied to any row in which the column A is filled with a name. 

 

All rows below the last worker's name must be deleted.

 

I guess I could solve some of that riddles but not all of them and I would really appreciate any help, so I can get that part of the big task solved.

 

Best regards!

 

In my example file I have defined the range A1:AK34 as a table  named Übersicht

Example 

 

Melanie

Hi @Anonymous 

 

Below is the solution. It has many steps and you can walkthrough it. Basically, I am trying to locate the "Ist" text and removing all columns before it. you have to do this for excel file which has 31 days for the first time or you can import additional blank columns while importing. later it will work for any number days.

 

You can update grouped rows step for more columns in query 1. 

 

Mark this as a solution if this gave an idea on how to do it.

 

query1

name=input

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VdfT9swEP8qVZ4rK/6TxHksIDaEihCFVQLxkEEmooVmSlsB321v+2K78zmN06alpKXTpLMd23f23f3s8+XuzvP63mn6vZwnZb8nfCGgfyC675vdTzKohlidFKhMCdUowQq7w+ITWD6iHoeCTpFQFJQASgglgqKhxMjjY4WcHFk58nJk5sjNkZ0jP0cBjhICJYRZGyUESpxNZ1CPB7dQ30A5RzuusXOKPeyOcPzmCLsDqC6QdYTV0a3Rd/jnd56ngOQweV1nWMj8YIPdEfP91gkuSZBLvY5lxX8XJTsbs14UysA4oDLLWGRsIzLGVfYZ04yR25NVyTYxNX5jULcNOrqOvl3+yD+06a6Eu35Np7Ni0u8dPyVlDl9GNXNsNBPmjDFFjZSutZGBKraMZFEgav6KUUl3RduzjI4HYHni4KJCWzBFhznsArfYQmTPrjw4gIopTTunb8/FHK7d5VOWZ788c8nsQZQGKCVdQbnUD5ior6RmmlCKLY4xgRIw6eIVmYDiowo0Ili4C17yfZFO5C+0US16/RPcIrww5vIV+WMKl2+c5XmWPK9hrrReGxlb5mw/JHh8unEyoNGuEIWHdNI+oRFV6A3eTy9EzWQgmj9OMT6eJ7PUW0R2OPehE+gbJytuvAHNRi3FvMhMSfEeo6IpYe8aFzHdzo5voYj/VyS3J0gXwEUuhFfFw09vERrrCKldMRv2IL4FtctjQmoVYk0AmrzKxk13Jd3YgHNBQHIed0FN7jk3bjpiidTa/ITbNIztW59VEgu/g+tkTGgmZZlNAc3sAVKWR285v2o2DhpbMG1yltopdspNKe8u1JpOtuJ2OMJdr9LXfu+6wDctZtF+Hne7UFcI9C57d3LC4YMeeohuyewpeUne+r3BZJLSJCTXlAxqXTfV1LqfroB8Dk+n8xegKV+0af1iCecHwa/ep05QKf6pXmrTqQNcdVYRbS+0skYlrDF9Juhe4LH6Aqlhlm6/kKa/Jb3ITxQ9Pro9SAMuEWXrnRIIpfYOSq1HW57eGaTdSaGn7u//Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t, Column27 = _t, Column28 = _t, Column29 = _t, Column30 = _t, Column31 = _t, Column32 = _t, Column33 = _t, Column34 = _t, Column35 = _t, Column36 = _t, Column37 = _t]),
monthname = Source{0}[Column2],
#"Removed Other Columns" = Table.SelectColumns(Source,columnnames[names]),
#"Removed Top Rows" = Table.Skip(#"Removed Other Columns",2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"", "Name"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Modulo"}),
#"Calculated Modulo" = Table.TransformColumns(#"Removed Columns", {{"Index", each Number.Mod(_, 3), type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Calculated Modulo", "Namenew", each if [Index] = 1 then [Name] else null),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Name"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Namenew", "Name"}}),
#"Filled Down" = Table.FillDown(#"Renamed Columns1",{"Name"}),
#"Added Custom" = Table.AddColumn(#"Filled Down", "Month", each monthname),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"BZ", type number}, {"SZ", type number}, {"NZ", type number}, {"KA", type number}, {"UB", type number}, {"SU", type number}, {"KK", type number}, {"UF", type number}, {"FT", type number}, {"K", type number}, {"U", type number}, {"WAZ", type number}, {"Ist", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"1st", each List.Max([Ist]), type nullable number}, {"WAZ", each List.Max([WAZ]), type nullable number}, {"U", each List.Max([U]), type nullable number}, {"K", each List.Max([K]), type nullable number}, {"FT", each List.Max([FT]), type nullable number}})
in
#"Grouped Rows"

 

 

query 2

name = columnnames

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VdfT9swEP8qVZ4rK/6TxHksIDaEihCFVQLxkEEmooVmSlsB321v+2K78zmN06alpKXTpLMd23f23f3s8+XuzvP63mn6vZwnZb8nfCGgfyC675vdTzKohlidFKhMCdUowQq7w+ITWD6iHoeCTpFQFJQASgglgqKhxMjjY4WcHFk58nJk5sjNkZ0jP0cBjhICJYRZGyUESpxNZ1CPB7dQ30A5RzuusXOKPeyOcPzmCLsDqC6QdYTV0a3Rd/jnd56ngOQweV1nWMj8YIPdEfP91gkuSZBLvY5lxX8XJTsbs14UysA4oDLLWGRsIzLGVfYZ04yR25NVyTYxNX5jULcNOrqOvl3+yD+06a6Eu35Np7Ni0u8dPyVlDl9GNXNsNBPmjDFFjZSutZGBKraMZFEgav6KUUl3RduzjI4HYHni4KJCWzBFhznsArfYQmTPrjw4gIopTTunb8/FHK7d5VOWZ788c8nsQZQGKCVdQbnUD5ior6RmmlCKLY4xgRIw6eIVmYDiowo0Ili4C17yfZFO5C+0US16/RPcIrww5vIV+WMKl2+c5XmWPK9hrrReGxlb5mw/JHh8unEyoNGuEIWHdNI+oRFV6A3eTy9EzWQgmj9OMT6eJ7PUW0R2OPehE+gbJytuvAHNRi3FvMhMSfEeo6IpYe8aFzHdzo5voYj/VyS3J0gXwEUuhFfFw09vERrrCKldMRv2IL4FtctjQmoVYk0AmrzKxk13Jd3YgHNBQHIed0FN7jk3bjpiidTa/ITbNIztW59VEgu/g+tkTGgmZZlNAc3sAVKWR285v2o2DhpbMG1yltopdspNKe8u1JpOtuJ2OMJdr9LXfu+6wDctZtF+Hne7UFcI9C57d3LC4YMeeohuyewpeUne+r3BZJLSJCTXlAxqXTfV1LqfroB8Dk+n8xegKV+0af1iCecHwa/ep05QKf6pXmrTqQNcdVYRbS+0skYlrDF9Juhe4LH6Aqlhlm6/kKa/Jb3ITxQ9Pro9SAMuEWXrnRIIpfYOSq1HW57eGaTdSaGn7u//Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t, Column27 = _t, Column28 = _t, Column29 = _t, Column30 = _t, Column31 = _t, Column32 = _t, Column33 = _t, Column34 = _t, Column35 = _t, Column36 = _t, Column37 = _t]),
#"Removed Top Rows" = Table.Skip(Source,2),
#"Kept First Rows" = Table.FirstN(#"Removed Top Rows",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "" or [Value] = "BZ" or [Value] = "FT" or [Value] = "Ist" or [Value] = "K" or [Value] = "KA" or [Value] = "KK" or [Value] = "NZ" or [Value] = "SU" or [Value] = "SZ" or [Value] = "U" or [Value] = "UB" or [Value] = "UF" or [Value] = "WAZ")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "names"}})
in
#"Renamed Columns"

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors