Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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/
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
@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/
Thanks to you 3!
I managed to get something working using @jthomson 's solution. Other 2 seemed to be working also.
Very appreciated!
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |