Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to import a multiline text record formated as follows:
ITEM VALUE
ITEM...........: Apple
SAttribute1..: 123
SAttribute2..: ABC
SAttribute3..: ZYX
MAttribute...: asdf
MAttribute...: qwer
MAttribute...: zxcv
ITEM...........: Banana
SAttribute1..: 654
SAttribute2..: CBA
SAttribute3..: XYZ
MAttribute...: asdf
MAttribute...: qwer
MAttribute...: zxcv
...
How do I import them to;
ITEM SAttribute1 SAttribute2 SAttribute3 MAttribute
Apples 123 ABC ZYX asdf,qwer,zxcv
Banana 321 CBA XYZ asdf,qwer,zxcv
Note that the source includes those dots to the fixed length position and those would be nice to remove. I was able to get the 2 columns @ the fixed location. I have no idea how to "flatten" (i know thats the wrong term) this. I am pretty sure every record includes the same 6 lines of attributes. HELP!!
Solved! Go to Solution.
Using the following 2 posts I was able to figure out using group, expand, and Pivot to get the result I was looking for.
https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864
I have added an index and a subsequent Rec number using a calculated column.
RecNo = Number.RoundUp([Index]/7)
I feel close but I still cant figure out how to flatten this.
ITEM VALUE Index RecNo
ITEM...........: Apple 1 1
SAttribute1..: 123 2 1
SAttribute2..: ABC 3 1
SAttribute3..: ZYX 4 1
MAttribute...: asdf 5 1
MAttribute...: qwer 6 1
MAttribute...: zxcv 7 1
ITEM...........: Banana 8 2
SAttribute1..: 654 9 2
SAttribute2..: CBA 10 2
SAttribute3..: XYZ 11 2
MAttribute...: asdf 12 2
MAttribute...: qwer 13 2
MAttribute...: zxcv 14 2
Using the following 2 posts I was able to figure out using group, expand, and Pivot to get the result I was looking for.
https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864
Are you using an imported model? In power query you can merge columns together, is this in a column format?
Proud to be a Super User!
Sorry I am not understanding your question. I suppose the merge might be the second part of my question if that is the part you are answering. But before I get there I would need to get here:
ITEM SAttribute1 SAttribute2 SAttribute3 MAttribute MAttribute MAttribute
Apples 123 ABC ZYX asdf qwer zxcv
Banana 321 CBA XYZ asdf qwer zxcv
How do I first "flatten" the file?
Here is my source line if that helps:
let
Source = Csv.Document(File.Contents("C:\path\srvtble.txt"),null,{0,40},null,1252),
...
Thanks for your time 🙂