Text file import - use data from the headers as a column

[RenaudB]

Hello everyone,

I have a new issue with a text file, I have little practice with text files and not sure how to achieve the result.

Here is my text file (excel):

Artikel	Mai 2020	Mai 2020	Mai 2020	Mai 2020
	Umsatz EK	Umsatz EK VJ	Absatz	Absatz VJ
1234	0	195	0	9
2345	1 456	1 743	30	37

Actually I need to use the "Mai 2020" which is unfortunately located in the header.

My final table in powerBI should be something like that:

Artikel	Umsatz EK	Umsatz EK VJ	Absatz	Absatz VJ	Date
1234	0	195	0	9	01/05/2020
2345	1 456	1 743	30	37	01/05/2020

I would like to fetch the value of the "Mai 2020" cell and add it as a new column.

I'm faily new to importing text files, could anyone direct me towards the functionality used for that in PowerBI ?

Thanks!

[jthomson · Accepted solution]

Extracting the value shouldn't be too hard - you could just duplicate the query, remove all but the top row, transpose the table, promote headers to get rid of the Artikel, retain just the top row and rename the column. Just a case then of joining it into the main table.

[mahoneypat · Accepted solution]

Please see this M code for an example of how to do this with your example data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Note that I had to add an extra step you probably won't need.  I have to convert the month from Mai to May, since my locale is different.  Delete that step when you try it.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSUQrNLU4sqVJw9UZmK4R5AbmOSSAunAESjNWJVjI0MjYBChoAsaGlKZRlCZYCyoAEDBVMTM3AtLmJMZA2BqkwNleKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Artikel = _t, #"Mai 2020" = _t, #"Mai 2020.1" = _t, #"Mai 2020.2" = _t, #"Mai 2020.3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Artikel", Int64.Type}, {"Mai 2020", type text}, {"Mai 2020.1", type text}, {"Mai 2020.2", type text}, {"Mai 2020.3", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.ColumnNames(#"Changed Type"){1}),
#"Promoted Headers" = Table.PromoteHeaders(#"Added Custom", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Umsatz EK", type text}, {"Umsatz EK VJ", type text}, {"Absatz", Int64.Type}, {"Absatz VJ", Int64.Type}, {"Mai 2020", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Artikel"}, {Table.ColumnNames(#"Changed Type1"){5}, "Date"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Mai","May",Replacer.ReplaceText,{"Date"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value", "Date2", each Date.FromText([Date])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Date2", type date}})
in
#"Changed Type2"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

Please see this M code for an example of how to do this with your example data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Note that I had to add an extra step you probably won't need.  I have to convert the month from Mai to May, since my locale is different.  Delete that step when you try it.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSUQrNLU4sqVJw9UZmK4R5AbmOSSAunAESjNWJVjI0MjYBChoAsaGlKZRlCZYCyoAEDBVMTM3AtLmJMZA2BqkwNleKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Artikel = _t, #"Mai 2020" = _t, #"Mai 2020.1" = _t, #"Mai 2020.2" = _t, #"Mai 2020.3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Artikel", Int64.Type}, {"Mai 2020", type text}, {"Mai 2020.1", type text}, {"Mai 2020.2", type text}, {"Mai 2020.3", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.ColumnNames(#"Changed Type"){1}),
#"Promoted Headers" = Table.PromoteHeaders(#"Added Custom", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", Int64.Type}, {"Umsatz EK", type text}, {"Umsatz EK VJ", type text}, {"Absatz", Int64.Type}, {"Absatz VJ", Int64.Type}, {"Mai 2020", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column1", "Artikel"}, {Table.ColumnNames(#"Changed Type1"){5}, "Date"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","Mai","May",Replacer.ReplaceText,{"Date"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value", "Date2", each Date.FromText([Date])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Date2", type date}})
in
#"Changed Type2"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[jthomson · Accepted solution]

Extracting the value shouldn't be too hard - you could just duplicate the query, remove all but the top row, transpose the table, promote headers to get rid of the Artikel, retain just the top row and rename the column. Just a case then of joining it into the main table.

[amitchandak · Accepted solution]

@jthomson , Nor very clear, Can you unpivot and use

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/

[RenaudB]

Thanks to you 3!

I managed to get something working using @jthomson 's solution. Other 2 seemed to be working also.

Very appreciated!