Help needed to transform data via M

[MaryF]

Hi all

I'm hoping someone can help me get past a big hurdle.. I have the following issue

My data is coming in as a single column

Raw Data

.I need it to become this...

Can anyone let me know if this is doable etc.

Many thanks in advance

Mary

[KHorseman · Accepted solution]

I don't know if this is the optimal solution, but I did it this way:

1. Added a custom column that repeats a sequence of the numbers 1 - 8 down the series so there is something to pivot on
2. Repeated the account name or whatever you call that first row in each set in your sample
3. Removed those first rows
4. Removed the index column because it will be meaningless after this
5. Pivoted on the column header number thing from step 2
6. The next steps would probably be to rename all these columns and maybe add a new index column, but I didn't bother.

let
    Source = Excel.Workbook(File.Contents("M:\Reports\test.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Index", Int64.Type}}),
    #"Added Sorter" = Table.AddColumn(
		#"Changed Type",
		"Sorter",
		each Number.From(
			List.Min(
				List.Range(
					List.Repeat(
						{1, 2, 3, 4, 5, 6, 7, 8},
						Int32.From(
							(List.Count(#"Changed Type"[Index]) / 8),
							null,
							RoundingMode.Up
						)		
					),
					[Index] - 1,
					1
				)
			)
		)
	),
    #"Added Name" = Table.AddColumn(
		#"Added Sorter",
		"Name",
		each Text.From(
			List.Last(
				List.FirstN(
					#"Added Sorter"[Column1],
					[Index] - ([Sorter] - 1)
				),
				1
			)
		)
	),
    #"Reordered Columns" = Table.ReorderColumns(
		#"Added Name",
		{"Index", "Sorter", "Name", "Column1"}
	),
    #"Removed Rows" = Table.SelectRows(
		#"Reordered Columns",
		each [Sorter] > 1
	),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Rows",{"Index"}),
    #"Pivoted Column" = Table.Pivot(
		Table.TransformColumnTypes(
			#"Removed Columns",
			{{"Sorter", type text}},
			"en-US"
		),
		List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Sorter", type text}}, "en-US")[Sorter]),
		"Sorter",
		"Column1"
	)
in
    #"Pivoted Column"

[KHorseman · Accepted solution]

I don't know if this is the optimal solution, but I did it this way:

1. Added a custom column that repeats a sequence of the numbers 1 - 8 down the series so there is something to pivot on
2. Repeated the account name or whatever you call that first row in each set in your sample
3. Removed those first rows
4. Removed the index column because it will be meaningless after this
5. Pivoted on the column header number thing from step 2
6. The next steps would probably be to rename all these columns and maybe add a new index column, but I didn't bother.

let
    Source = Excel.Workbook(File.Contents("M:\Reports\test.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Index", Int64.Type}}),
    #"Added Sorter" = Table.AddColumn(
		#"Changed Type",
		"Sorter",
		each Number.From(
			List.Min(
				List.Range(
					List.Repeat(
						{1, 2, 3, 4, 5, 6, 7, 8},
						Int32.From(
							(List.Count(#"Changed Type"[Index]) / 8),
							null,
							RoundingMode.Up
						)		
					),
					[Index] - 1,
					1
				)
			)
		)
	),
    #"Added Name" = Table.AddColumn(
		#"Added Sorter",
		"Name",
		each Text.From(
			List.Last(
				List.FirstN(
					#"Added Sorter"[Column1],
					[Index] - ([Sorter] - 1)
				),
				1
			)
		)
	),
    #"Reordered Columns" = Table.ReorderColumns(
		#"Added Name",
		{"Index", "Sorter", "Name", "Column1"}
	),
    #"Removed Rows" = Table.SelectRows(
		#"Reordered Columns",
		each [Sorter] > 1
	),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Rows",{"Index"}),
    #"Pivoted Column" = Table.Pivot(
		Table.TransformColumnTypes(
			#"Removed Columns",
			{{"Sorter", type text}},
			"en-US"
		),
		List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Sorter", type text}}, "en-US")[Sorter]),
		"Sorter",
		"Column1"
	)
in
    #"Pivoted Column"

[MaryF]

@KHorseman Thanks LOADS!!! Very much appriate your input. Mary 🙂

[KHorseman]

Thanks. It was a fun problem to tackle. I'm sure there's a more efficient way to do the Sorter column, but that was the best I could think of. I'd be interested to see if someone else has a different solution to this.

[ImkeF · Accepted solution]

This is very similar, but a bit shorter:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    RowNumber = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/8)),
    PivotCols = Table.AddColumn(RowNumber, "Pivot", each Number.Mod([Index], 8)),
    RemoveIndex = Table.RemoveColumns(PivotCols,{"Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(RemoveIndex, {{"Pivot", type text}}, "de-DE"), List.Distinct(Table.TransformColumnTypes(RemoveIndex, {{"Pivot", type text}}, "de-DE")[Pivot]), "Pivot", "Spalte1")
in
    #"Pivoted Column"

[KHorseman]

@ImkeF I knew there had to be an easier way to get the 1-8 count. Math, of course! Nicely done.

@MaryF you're welcome. I learned a lot here too.

[MaryF]

Thanks loads @ImkeF. I am so pleased to have a solution plus I learnt alot too. 

[MaryF]

Sorry I should have said thank you @KHorseman & @ImkeF as I have two solutions to learn new stuff from.